Forum Discussion

kerch007's avatar
kerch007
Frequent Visitor
5 years ago
Solved

How to filter a table

Hi. Please help me make a measure that would filter my table. The table should contain only values "group-1" in which there are all values from selected in slicer "group-2"   My table: Group-...
Filter
Filtering
  • AlB's avatar
    AlB
    5 years ago

    kerch007 

    Small change at the end:

    ShowMeasure V2= 
VAR G2inTable_ =
    CALCULATETABLE ( DISTINCT ( MyTable[Group-2] ), ALL ( MyTable[Group-2] ) )
VAR G2InSlicer_ =
    DISTINCT ( SlicerT[Group-2] )
VAR allPresent_ =
    COUNTROWS ( INTERSECT ( G2inTable_, G2InSlicer_ ) ) = COUNTROWS ( G2InSlicer_ )
RETURN
    IF ( (allPresent_ || NOT ISFILTERED ( SlicerT[Group-2] )) && SELECTEDVALUE(MyTable[Group-2]) IN G2InSlicer_ , 1, 0 )

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

AlB's avatar
AlB
Community Champion
5 years ago

Hi kerch007 

1. Create a new one-column table with all the values of Group-2 that will be used for the slicer. Let's call it SlicerT

2. Create this measure: 

 

ShowMeasure =
VAR G2inTable_ =
    CALCULATETABLE ( DISTINCT ( MyTable[Group-2] ), ALL ( MyTable[Group-2] ) )
VAR G2InSlicer_ =
    DISTINCT ( SlicerT[Group-2] )
VAR allPresent_ =
    COUNTROWS ( INTERSECT ( G2inTable_, G2InSlicer_ ) ) = COUNTROWS ( G2InSlicer_ )
RETURN
    IF ( allPresent_ || NOT ISFILTERED ( SlicerT[Group-2] ), 1, 0 )

 

3. Assuming you have the visual you show in place, place [ShowMeasure] in the filters of that visual and select to show when the value of the measure is 1

 

See it all at play in the attached file.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

  • kerch007's avatar
    kerch007
    Frequent Visitor
    5 years ago

    Many thanks!
    Almost what I need.
    Please tell me how to remove unnecessary values that are not in the slicer?

    Screen: https://ibb.co/ZV1vP5g 

    • AlB's avatar
      AlB
      Community Champion
      5 years ago

      kerch007 

      Small change at the end:

      ShowMeasure V2= 
VAR G2inTable_ =
    CALCULATETABLE ( DISTINCT ( MyTable[Group-2] ), ALL ( MyTable[Group-2] ) )
VAR G2InSlicer_ =
    DISTINCT ( SlicerT[Group-2] )
VAR allPresent_ =
    COUNTROWS ( INTERSECT ( G2inTable_, G2InSlicer_ ) ) = COUNTROWS ( G2InSlicer_ )
RETURN
    IF ( (allPresent_ || NOT ISFILTERED ( SlicerT[Group-2] )) && SELECTEDVALUE(MyTable[Group-2]) IN G2InSlicer_ , 1, 0 )

       

      Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

      Contact me privately for support with any larger-scale BI needs, tutoring, etc.

      Cheers 

      • kerch007's avatar
        kerch007
        Frequent Visitor
        5 years ago
        Exactly what I need! Thanks!