Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
smpa01
Super User
Super User

how to pass on multiple slicer values dynamically to DAX IN operator

My source data is following

 

| Year | month | val |
|------|-------|-----|
| 2020 | 1     | 100 |
| 2020 | 2     | 200 |
| 2020 | 3     | 300 |
| 2020 | 4     | 400 |
| 2020 | 5     | 500 |
| 2020 | 6     | 600 |
| 2020 | 7     | 700 |
| 2021 | 1     | 400 |
| 2021 | 2     | 500 |
| 2021 | 3     | 600 |

 

I have a viz like following

smpa01_2-1637677533104.png

 

 

All I want is

DAX to

     return sum=300 for all rows

    when

    Year=2020 and Month=1,2

   and

     return sum=600 for all rows

    when

    Year=2020 and Month=1,2,3

 and so on and so forth

My desired result is following

smpa01_1-1637677466183.png

 

smpa01_3-1637677794960.png

which I can achieve if I write a measure like following

 

Measure = CALCULATE(SUM('Table'[val]),ALLEXCEPT('Table','Table'[Year]),'Table'[month] IN {1,2})

 

But I was wondering , is there a way to pass on the slicer selections to IN dynamically, cause when I select month=1,2,3 the measure falls flat

smpa01_4-1637677982028.png

 

As much as I want to know if there is way to pass on the slicer selections to IN, if there is another way that can give me the end result, I would be interested in that too.

The desired result is following

DAX to filter the table as per the slicer selection and perform an ALL within the subset returned by slicer, so that it returns the following

smpa01_6-1637678293225.png

 pbix is attached

Thank you in advance

@AlexisOlson 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

I'd recommend using ALLSELECTED with a variable:

 

VAR SelectedMonths = ALLSELECTED ( 'Table'[month] )
RETURN
    CALCULATE (
        SUM ( 'Table'[val] ),
        'Table'[month] IN SelectedMonths
    )

 

You could also write it like this:

VAR SelectedMonths = ALLSELECTED ( 'Table'[month] )
RETURN
    CALCULATE (
		SUM ( 'Table'[val] ),
		TREATAS ( SelectedMonths, 'Table'[month] )
	)

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

I'd recommend using ALLSELECTED with a variable:

 

VAR SelectedMonths = ALLSELECTED ( 'Table'[month] )
RETURN
    CALCULATE (
        SUM ( 'Table'[val] ),
        'Table'[month] IN SelectedMonths
    )

 

You could also write it like this:

VAR SelectedMonths = ALLSELECTED ( 'Table'[month] )
RETURN
    CALCULATE (
		SUM ( 'Table'[val] ),
		TREATAS ( SelectedMonths, 'Table'[month] )
	)

Awesome @AlexisOlson !! Thanks a lot

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.