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
backflash
Helper II
Helper II

Use Measure Values in Slicer (multi select)

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.

 

backflash_0-1666007609347.png

 

 

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

backflash_1-1666007718612.png

 

 

 

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

 

 

2 ACCEPTED SOLUTIONS
backflash
Helper II
Helper II

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:
 
With my measures in place I then created a table with values and corresponding slicers...
 
this video shows very nice how to do it:
 
 
 
 
 
 

 

View solution in original post

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_0-1666871618346.png

 

View solution in original post

4 REPLIES 4
backflash
Helper II
Helper II

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:
 
With my measures in place I then created a table with values and corresponding slicers...
 
this video shows very nice how to do it:
 
 
 
 
 
 

 

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_0-1666871618346.png

 

backflash
Helper II
Helper II

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.