topic Re: dynamic chart to allow multiple selections from two (or more) slicers in DAX Commands and Tips

dynamic chart to allow multiple selections from two (or more) slicers

leeham — Wed, 05 Oct 2022 14:59:52 GMT

hi,

I am having trouble being able to select multiple options from a slicer and this being recognised in measure and then a line chart that feeds off of the output.
I have been successful with the chart working when selecting only one item from the slicer, the moment i choose more than one it breaks. I used selectvalue to make it work for one selection per slicer.

I have a line chart that has date in the X axis (from 2019+) .
My Y axis is a calculation using two measures.
Calculation:
numerator = total balance with the two slicers and the chosen selections in each applied to it [not working]
denominator = total balance ignoring the two slicers options chosen [this is working fine]

My numerator has two slicers for the viewer to choose from:
slicer1 = status of the customers this month [TEXT: "up to date", "1", "2" etc.]

slicer2 = status of the customers last month [TEXT: "up to date", "1", "2" etc.]

I used selectvalue with DAX code and this allowed one selection from slicer1 and from slicer2.
I need the ability for the user to be able to choose multiple options from slicer1 and slicer2.
Any help on what I need to tweak to the code would be greatly appreciated.

Example dax code:

bal num =

var slicer1= SELECTEDVALUE(table1[status_now])

var slicer2= SELECTEDVALUE(table1[status_prev])

return

CALCULATE(sum(table1[balance])

,table1[status_now] = slicer1

,table1[status_prev] = slicer2

)

Re: dynamic chart to allow multiple selections from two (or more) slicers

johnt75 — Wed, 05 Oct 2022 15:50:44 GMT

Instead of SELECTEDVALUE you need to use VALUES, and because that returns a table you need to use IN instead of =, e.g.

bal num = VAR slicer1 = VALUES ( table1[status_now] ) VAR slicer2 = VALUES ( table1[status_prev] ) RETURN CALCULATE ( SUM ( table1[balance] ), table1[status_now] IN slicer1, table1[status_prev] IN slicer2 )

Re: dynamic chart to allow multiple selections from two (or more) slicers

leeham — Wed, 05 Oct 2022 16:10:32 GMT

Thanks Johnt75 - that worked!

Do you happen to know the difference between your solution and this?

It looks to give the same or similar answer but something feels off with the below version.

bal num =
var slicer1= SELECTEDVALUE(table1[status_now])
var slicer2= SELECTEDVALUE(table1[status_prev])
return
CALCULATE(sum(table1[balance])
,ALLSELECTED(table1[status_now])
,ALLSELECTED(table1[status_prev])
)

Re: dynamic chart to allow multiple selections from two (or more) slicers

johnt75 — Wed, 05 Oct 2022 16:20:13 GMT

ALLSELECTED removes any filters which are applied by the current visual but retains filters which are applied externally. For example, if you had a table visual showing a split by category, which was also affected by a slicer on category, then inside the table visual ALLSELECTED would remove the filter on the current category for that row but would retain the filtered categories from the slicer.

VALUES will not remove any filters, it will always show those values which are available in the current filter context. So in the previous example, within a table visual split by category, on each row VALUES would only return the current category.

If no filters are applied within the visual then the results would be the same.

Re: dynamic chart to allow multiple selections from two (or more) slicers

leeham — Wed, 05 Oct 2022 16:24:29 GMT

Thanks again.

There are other filter/slicers within the main visual (product and other things) which are meant to alter both the numerator and the denominator.
I think your solution allows for these other filters from what you've said which is perfect.