Select multiple values from slicer for DAX formula

[Joe_100]

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!

[TomMartens · Accepted solution]

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

[TomMartens · Accepted solution]

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

[Joe_100]

@TomMartens

Thanks, this works like a charm 🙂

[Joe_100]

@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?

[TomMartens]

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

Regards
Tom

[Joe_100 · Accepted solution]

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(..................

[TomMartens]

Perfect!

[Zubair_Muhammad]

@Joe_100

Try with following

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