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!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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