cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver III

## List items that match all the selected values

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

1 ACCEPTED SOLUTION
Solution Sage

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

5 REPLIES 5
Resolver III

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.

Solution Sage

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

Solution Sage

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

Resolver III

Thanks @Barthel

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

Solution Sage

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