List items that match all the selected values

[AnthonyJoseph]

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

[AnthonyJoseph]

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.

[Barthel]

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" )

[Barthel · Accepted solution]

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" )

[AnthonyJoseph]

Thanks @Barthel 

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

[Barthel]

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" )