Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
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
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
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
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
pbix is attached
Thank you in advance
Solved! Go to Solution.
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] )
)
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
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.