Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |