Forum Discussion
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-1 | Group-2 | Value |
| 111 | aaa | 10 |
| 111 | bbb | 20 |
| 111 | ccc | 25 |
| 222 | aaa | 30 |
| 222 | ccc | 40 |
| 222 | ddd | 45 |
| 333 | aaa | 50 |
| 333 | bbb | 55 |
Exemple1
Example 2
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
5 Replies
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
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
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
Hi, kerch007 , you might want to try this measure, which isn't that elegant😂
Val = VAR __sel = ALLSELECTED ( Selector[Group-2] ) VAR __lv1 = CALCULATE ( DISTINCTCOUNT ( DS[Group-2] ), TREATAS ( DISTINCT ( Selector[Group-2] ), DS[Group-2] ) ) = COUNTROWS ( __sel ) VAR __lv2 = NOT ISBLANK ( IF ( ISINSCOPE ( DS[Group-2] ), INTERSECT ( DISTINCT ( DS[Group-2] ), __sel ) ) ) RETURN IF ( __lv1 && __lv2, MAX ( DS[Value] ) )An attached file is at your disposal for more details.
