Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
OskariNi
Helper I
Helper I

Filter table based on variable table column

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
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
Icey
Community Support
Community Support

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
Helper I
Helper I

Please find some dummy table to better describe the question:

 

Table1 = table that is a variable in measure calculation:

Column1Column2

A

X
AY
CZ

 

Table2 = table to be filtered 

Column1Column2Column3
A16
B27
B38
C49
C510

 

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

vanessafvg
Super User
Super User

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.