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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Joe_100
Helper I
Helper I

Select multiple values from slicer for DAX formula

Hi all,

 

At the moment i have a what if parameter so the users can select a month in a slicer, that parameter will be used in another dax formula.

 

I generate 12 month numbers and select the value from the slicer with the following DAX

 

MonthParValue = SELECTEDVALUE(MonthParameter[MonthPar])

 

After that i use that value in the following dax to select on a month basis.

 

EstimateCurrentMonth = calculate(sum('Sales'[Estimate]);FILTER(ALL('Sales'[MonthNumber]);[MonthNumber] = 'MonthParameter'[MonthParValue]))

 

this is working fine, but only if i select one month. I see that the selectedvalue returns only one value.

 

How can i change this so i can select multiple values from the slicer?

 

Thanks!

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey,

 

I guess if you replace your estimation measure

EstimateCurrentMonth = calculate(sum('Sales'[Estimate]);FILTER(ALL('Sales'[MonthNumber]);[MonthNumber] = 'MonthParameter'[MonthParValue]))

with this

EstimateCurrentMonth = 
var selectedMonths = Values('MonthParameter'[MonthParValue])
return
calculate(
	sum('Sales'[Estimate])
	;FILTER(
		ALL('Sales'[MonthNumber])
		;[MonthNumber] IN selectedMonths
	)
)

You are good to go.

 

Please be aware that the variable selectedMonths contains a table and no loger a scalar value. You also have to make sure that the slicer with the Month numbers has to be enabled for multi selection

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hi @TomMartens

 

Thanks, but i think i fixed it fairly easily.

 

I created a measure 

 

IsFiltered = ISCROSSFILTERED(MonthParameter[MonthPar])

 

In the other DAX a simple IF check seems to work fine..

 

IF(MonthNamesNumbersAsVar[IsFiltered] = TRUE;
calculate(..................

View solution in original post

7 REPLIES 7
TomMartens
Super User
Super User

Hey,

 

I guess if you replace your estimation measure

EstimateCurrentMonth = calculate(sum('Sales'[Estimate]);FILTER(ALL('Sales'[MonthNumber]);[MonthNumber] = 'MonthParameter'[MonthParValue]))

with this

EstimateCurrentMonth = 
var selectedMonths = Values('MonthParameter'[MonthParValue])
return
calculate(
	sum('Sales'[Estimate])
	;FILTER(
		ALL('Sales'[MonthNumber])
		;[MonthNumber] IN selectedMonths
	)
)

You are good to go.

 

Please be aware that the variable selectedMonths contains a table and no loger a scalar value. You also have to make sure that the slicer with the Month numbers has to be enabled for multi selection

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens

 

Thanks, this works like a charm 🙂

@TomMartens

One other question, i see that the slicer returns all values as default, how can i set it so the slicer returns "nothing"  as default?

Unfortunately you can't. Nothing selected means all is selected. That's how slicer work.

Regards
Tom


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens

 

Thanks, but i think i fixed it fairly easily.

 

I created a measure 

 

IsFiltered = ISCROSSFILTERED(MonthParameter[MonthPar])

 

In the other DAX a simple IF check seems to work fine..

 

IF(MonthNamesNumbersAsVar[IsFiltered] = TRUE;
calculate(..................

Perfect!



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Zubair_Muhammad
Community Champion
Community Champion

@Joe_100

 

Try with following

 

EstimateCurrentMonth =
CALCULATE (
    SUM ( 'Sales'[Estimate] ),
    FILTER (
        ALL ( 'Sales'[MonthNumber] ),
        [MonthNumber] IN VALUES ( 'MonthParameter'[MonthParValue] )
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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