cancel
Showing results for 
Search instead for 
Did you mean: 
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!






New Animated Dashboard: Sales Calendar


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!






New Animated Dashboard: Sales Calendar


Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors