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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.