topic Re: How to filter a table in DAX Commands and Tips

How to filter a table

kerch007 — Thu, 22 Oct 2020 15:59:10 GMT

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

Re: How to filter a table

AlB — Thu, 22 Oct 2020 19:19:13 GMT

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

Re: How to filter a table

kerch007 — Thu, 22 Oct 2020 19:57:23 GMT

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

Re: How to filter a table

CNENFRNL — Thu, 22 Oct 2020 20:38:41 GMT

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.

Re: How to filter a table

AlB — Thu, 22 Oct 2020 20:44:27 GMT

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

Re: How to filter a table

kerch007 — Thu, 22 Oct 2020 20:51:57 GMT

Exactly what I need! Thanks!