Forum Discussion

OskariNi's avatar
OskariNi
Icon for Helper I rankHelper I
4 years ago
Solved

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,...
Need Help
  • Icey's avatar
    Icey
    4 years ago

    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.

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
4 years ago

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.