topic Re: List items that match all the selected values in DAX Commands and Tips

List items that match all the selected values

AnthonyJoseph — Fri, 05 Aug 2022 23:06:31 GMT

Hello Community,

I am trying to get the list of Items based on the year selection i.e. when I select

"2022 and 2021" in the year slicer. Then I should get the Item that are names that are in both (2022 and 2021 years) i.e. Apple and Cucumber rows should get a value "Y", else "N".

Year .Slicer = 2022 and 2021

Cat	Item	Year	unit	Sales	Output
A	Apple	2022	100	200	Y
A	Apple	2021	200	400	Y
A	Apple	2020	500	1000	N
A	Papaya	2021	100	400	N
A	Papaya	2020	40	160	N
V	Cucumber	2022	150	500	Y
V	Cucumber	2021	250	800	Y

If I select 2020, 2021,2022 years then I should have only Apple (because only Apple is present in all 3 years)

Cat	Item	Year	Unit	Sales	Output
A	Apple	2022	100	200	Y
A	Apple	2021	200	400	Y
A	Apple	2020	500	1000	Y
A	Papaya	2021	100	400	N
A	Papaya	2020	40	160	N
V	Cucumber	2022	150	500	N
V	Cucumber	2021	250	800	N

Please can anyone help me in achiving this measure.

Thanks,

Anthony

Re: List items that match all the selected values

Barthel — Sat, 06 Aug 2022 08:21:25 GMT

Hey,

Make sure your year slicer table is not connected to your fact table through a relationship (let it be disconnected). Then you can use this code for a measure:

Output = IF ( SELECTEDVALUE ( Table[Year] ) IN VALUES ( YearSlicer[Year] ), "Y", "N" )

Re: List items that match all the selected values

AnthonyJoseph — Mon, 08 Aug 2022 05:57:14 GMT

Thanks Barthel

But still the output is not as expected. Example, Papaya for year 2021 should be N but measure gives me Y.

Re: List items that match all the selected values

Barthel — Mon, 08 Aug 2022 07:23:27 GMT

Hey AnthonyJoseph,

I misread your question. This code should provide the desired result:

Output = VAR _YearSlicer = VALUES ( Yearslicer[Year] ) VAR _YearsByItem = CALCULATETABLE ( VALUES ( 'Table'[Year] ), VALUES ( 'Table'[Item] ), ALLSELECTED () ) VAR _Intersect = INTERSECT ( _YearSlicer, _YearsByItem ) VAR _Result = COUNTROWS ( _YearSlicer ) = COUNTROWS ( _Intersect ) RETURN IF ( _Result, "Y", "N" )

Re: List items that match all the selected values

AnthonyJoseph — Wed, 10 Aug 2022 09:35:44 GMT

Thanks Barthel ,

Its still not working as expected.

even when I select the year 2021, I am seeing the other year values also which should not be the case... Any way to filter only the selected list of values...

I think slicer should act more like a AND operation rather than OR.

Re: List items that match all the selected values

Barthel — Wed, 10 Aug 2022 09:52:08 GMT

Hey AnthonyJoseph,

Yeah you are right. Three times a charm:

Output = VAR _YearSlicer = VALUES ( Yearslicer[Year] ) VAR _YearsByItem = CALCULATETABLE ( VALUES ( 'Table'[Year] ), VALUES ( 'Table'[Item] ), ALLSELECTED () ) VAR _Intersect = INTERSECT ( _YearSlicer, _YearsByItem ) VAR _Filter1 = COUNTROWS ( _YearSlicer ) = COUNTROWS ( _Intersect ) VAR _Filter2 = SELECTEDVALUE ( 'Table'[Year] ) IN _YearSlicer RETURN IF ( _Filter1 && _Filter2, "Y", "N" )