Skip to main content
cancel
Showing results for 
Search instead 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

Reply
AnthonyJoseph
Resolver III
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
Barthel
Solution Sage
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" )

 

 

 

View solution in original post

5 REPLIES 5
AnthonyJoseph
Resolver III
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.

AnthonyJoseph_0-1660124034170.png

 

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

 

 

 

Thanks @Barthel 

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

AnthonyJoseph_0-1659938089912.png

 

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.