cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leeham
Frequent Visitor

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

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
 )

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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
    )

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

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
    )

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])
)

 

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.

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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors