Forum Discussion
Resolver IIIList 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
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
- Barthel
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" )
AnthonyJosephThanks 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" )
- 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" )