Use Measure Values in Slicer (multi select)

[backflash]

Hey All,
I am currently trying to use my measure results in a slicer. I already checked out some posts and I saw a solution for building this with the help of an additional table and two add. measures (this great video https://www.youtube.com/watch?v=AZAL-QPn5Zc) - but this was an example for integer based values of a measure.

I have the scenario that my measure containes string - and I was able to use it in a slicer, but can only select one value (and slicer is filtering by measure value correctly) but when I try to select more than one option, it doesn't work anymore.

Maybe someone has an idea how I could change my DAX in order to make it 'multi select'.

Let me show you what I've got so far:

I have a measure that is -based on other measures - clustering with a specific  string.

As all measures all with German words I will avoid screenshots and I will try to explain what the measures are doing, so you can follow along.

The Measure LatestOrder  is assigning a cluster name based on the result of other measures.

[Latest order] = SWITCH([MeasureCase1]>0, "Case1", [MeasureCase2]>0, "Case2", [MeasureCase3], "Case3", ..., [MeasureCase9]>0, "Case9")

The measure is working fine, so I can cluster correctly if a customer is case1, case2 (...) or case9.

Now I want to use the measure results "Case1, "Case2", ... in a slicer.

For this, I created a table and the table contained each possible string my measure can have as a row:

'ClusterTable'[Value]:
Case1

Case2

Case3

...

Case9

Now I can use the column in a slicer.

To connect my slicer to the measure results, I need to create a new measure that says:

[MeasureFilter] =

VAR selectedValue = SELECTEDVALUE('ClusterTable'[Value])

VAR CurrentValue = [Latest order]

RETURN

IF(CurrentValue==selectedValue, 1, 0

)

Then I add the SlicerMeasure as a filter to the relevant visuals and say 'show only when is '1' --> this way, I filter it to the selected value.

This works fine - but only when one option is selected (because only then my CurrentValue==selectedValue works)

Does someone has an idea how I could write the measure code in order to have multi selects allowed?

I am thinking of an array but cannot bring it into DAX.

Looking forward to your ideas.

Regards

Vanessa

[backflash · Accepted solution]

I solved it now with another 'workaround' - so if someone has a similar problem, maybe this helps?!

I changed my measure from text/string values to a numeric approach.- Instead of clustering the last order with strings like 'Last month', 'this month', .... I simply calculated the amount of days from the last order. This way, I was able to use a slicer with integer values to filter and this solved my problems!

I did this with the help of the following DAX measures:
LatestDateOrder =LASTDATE(Orders[OrderDate])

DaysSinceLastOrder = DATEDIFF([LatestDateOrder], TODAY(), DAY)

Since I grouped the values of my measure 'DaysSinceLastOrder' in a matrix by product.group, I needed a third measure:
MaxDaysSinceLastOrder = MAXX(product, [DaysSinceLastOrder])

Otherwise it would have clculated the DaysSinceLastOrder for the group (latest order of product group would have been taken - but I wanted to have the oldest possible order - so the maximum of all latest orders... not the latest latest order)

And I learned that it is not easy to use MAX() on a measure - this is why I searched for some tipps and got this very helpful video:

https://www.youtube.com/watch?v=DAvzV-4Li7A

With my measures in place I then created a table with values and corresponding slicers...

this video shows very nice how to do it:

https://www.youtube.com/watch?v=AZAL-QPn5Zc

[backflash · Accepted solution]

I solved it now with another 'workaround' - so if someone has a similar problem, maybe this helps?!

I changed my measure from text/string values to a numeric approach.- Instead of clustering the last order with strings like 'Last month', 'this month', .... I simply calculated the amount of days from the last order. This way, I was able to use a slicer with integer values to filter and this solved my problems!

I did this with the help of the following DAX measures:
LatestDateOrder =LASTDATE(Orders[OrderDate])

DaysSinceLastOrder = DATEDIFF([LatestDateOrder], TODAY(), DAY)

Since I grouped the values of my measure 'DaysSinceLastOrder' in a matrix by product.group, I needed a third measure:
MaxDaysSinceLastOrder = MAXX(product, [DaysSinceLastOrder])

Otherwise it would have clculated the DaysSinceLastOrder for the group (latest order of product group would have been taken - but I wanted to have the oldest possible order - so the maximum of all latest orders... not the latest latest order)

And I learned that it is not easy to use MAX() on a measure - this is why I searched for some tipps and got this very helpful video:

https://www.youtube.com/watch?v=DAvzV-4Li7A

With my measures in place I then created a table with values and corresponding slicers...

this video shows very nice how to do it:

https://www.youtube.com/watch?v=AZAL-QPn5Zc

[backflash · Accepted solution]

Another nice solution I found (after changing it all from text-measure to integer-value-measure) is the following... it does not allow to use multiple option as selection in the slicer, but due to the 'all customer' option it provides more flexibility than just the single selection.

If you want to check it out in detail, watch the video: https://www.youtube.com/watch?v=AHiCE1N0XHE

Here you can see upfront, what's it ll about:

[backflash]

Hi @Greg_Deckler ,

thanks a lot for suggesting this article. Really worth a read and I understand the way of doing it and tried it with my example - but still the problem: works fine if only one value is selected - and does not work anymore when two or more options are selected. I assume this is because it is realized also with a SWITCH-case that only can have one option is true.

Do you have an idea how I could change this in order to have two or more values true the same time?

[Greg_Deckler]

@backflash In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...