Filter table based on variable table column

[OskariNi]

Hi! 

I'm struggling to come up with a solution on how to filter a table based on values in a column of a variable/calculated table. 

So I was thinking of something like this: 

FILTER(Table, Table[Column] IN VALUES(VarTable))

But I'm not able to refer to a specific column inside the VarTable. Any ideas? 

Thanks, Oskari

Full measure

Measure =

VAR Selected = ALLSELECTED(Table[Col], Table[Col2])

VAR AllRows = ALL(Table[Col], Table[Col2])

VAR ExcludeRows = EXCEPT ( AllRows, Selected )

VAR AffectedRows = CALCULATETABLE(ExcludeRows, KEEPFILTERS ( ExcludeRows) )

var row_count = COUNTROWS(FILTER(Table, Table[Col] IN VALUES(AffectedRows))

return row_count

[Icey · Accepted solution]

Hi @OskariNi ,

Try this:

Measure =

VAR Selected = ALLSELECTED(Table[Col], Table[Col2])

VAR AllRows = ALL(Table[Col], Table[Col2])

VAR ExcludeRows = EXCEPT ( AllRows, Selected )

VAR AffectedRows =CALCULATETABLE(SUMMARIZE(ExcludeRows,[Col]), KEEPFILTERS ( ExcludeRows))

var row_count = COUNTROWS(FILTER(Table, Table[Col] IN VALUES(AffectedRows))

return row_count

Or this:

Measure =

VAR Selected = ALLSELECTED(Table[Col], Table[Col2])

VAR AllRows = ALL(Table[Col], Table[Col2])

VAR ExcludeRows = EXCEPT ( AllRows, Selected )

VAR AffectedRows =CALCULATETABLE(ExcludeRows, KEEPFILTERS ( ExcludeRows))

VAR t_ = SUMMARIZE(ExcludeRows,[Col])

var row_count = COUNTROWS(FILTER(Table, Table[Col] IN t_)

return row_count

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OskariNi]

Please find some dummy table to better describe the question:

Table1 = table that is a variable in measure calculation:

Column1	Column2
A	X
A	Y
C	Z

Table2 = table to be filtered 

Column1	Column2	Column3
A	1	6
B	2	7
B	3	8
C	4	9
C	5	10

So the desired outcome is to filter Table2 with the condition that Column1 value is in the Column1 of Table1.

[vanessafvg]

can you give an example of what you are asking,as its easier to understand what you are asking for that way.

provide some dummy data or actual data

and then an example of what your expected solution is.