Hi,
I have posted, almost the same question on Mr Excel ;
https://www.mrexcel.com/board/thread...table.1252088/
I would like to filter a table down twice and see the "Item" values that are in both, in power query which I have attacched;
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Blue = Table.SelectRows(Source , each ([Color] = "Blue")),
Green = Table.SelectRows( Source , each List.Contains( {"Green"},[Color] ) ),
Custom1 = Table.SelectRows( Green, each List.ContainsAny( Blue[Item] , {[Item] } ) )
in
Custom1
But I have been trying to do this in DAX but have two problems,
I can get what I want by filtering and then taking the values and hardcoding them in so A, B and C values returned in 'atable' ;
EVALUATE
VAR atable =
CALCULATETABLE ( FILTER ( ALL ( TableA ), TableA[Color] = "Green" ) )
VAR btable =
CALCULATETABLE (
SUMMARIZE ( TableA, TableA[Color], TableA[Item] ),
TableA[Color] = "Blue"
)
VAR lookupV =
SUMMARIZE ( btable, [Item] ) //not used in return
VAR anser1 =
FILTER ( atable, [Item] = "D" || [Item] = "C" )
RETURN
anser1
But what I am trying for is someting along the lines of ;
CALCULATETABLE( atable , [Item] IN lookupV )
I think that SUMMARIZE( btable,[Item] ) would give same as { "D","C"}
I have go a result using Filter and Or ||
I have attached my starting point, what I've done so far has been in studio rather than on file .
Richard
Bookmarks