Forum Discussion

OskariNi's avatar
OskariNi
Helper 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, 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
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.

3 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    4 years ago

    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.

  • OskariNi's avatar
    OskariNi
    Helper I
    4 years ago

    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.

  • Icey's avatar
    Icey
    Community 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.