Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
yybi123
Helper I
Helper I

How to use a measure as slicer?

Hi, I have a measure which categorise my data based on few statuses. How can I use this status measure as a slicer?

Status = IF(ISBLANK(Difference),"Gray", IF(Difference>0, "Green", IF(Difference<0, "Red", "Amber")))

- Difference is a measure as well-


I need a slicer with Gray, Green, Red , Amber so users can filter the data base on this. I have to use a measure for the status as a calulated column will not give me the desired results. I cant drag the measure as a slicer.
Thanks.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @yybi123 ,

 

Based on your description, you can so some steps as follows.

Here is my test table.

v-yuaj-msft_0-1616135482438.png

I create two measures as follows.

Difference = MAXX('Measure_Slicer_test',[D2]-[D1])
Status = IF(ISBLANK([Difference]),"Gray", IF([Difference]>0, "Green", IF([Difference]<0, "Red", "Amber")))
1. Create a calculated table.
Status = 

var x1=SUMMARIZE('Measure_Slicer_test',Measure_Slicer_test[Product],"Status",[Status])

return

SUMMARIZE(x1,[Status])

v-yuaj-msft_0-1616135592563.png

2. use "Status"[Status] to create a slicer.

v-yuaj-msft_1-1616135712903.png

3. create a measure, then drag it to the table visual of the original table and set the value as "1".

Measure_filter = 

var x1=SELECTEDVALUE('Status'[Status])

return

IF(ISBLANK(x1),1,IF([Status]=x1,1,0))

v-yuaj-msft_2-1616135828497.png

Result:

 031901.gif

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

13 REPLIES 13
AnjanaTh
Frequent Visitor

I want to use a total score measure is a slicer , which consist of another measure whose values depends upon the selected value of other columns slicer .The above solution is not working as my measure is a dynamic one based on another measure which is based on slicer selection by user.Is there any workaroudn for this?

 

SaadNaser
Regular Visitor

great job mate, this is very usefull!!

forgetmenot
Helper I
Helper I

Can the same princple be applied to a Matrix? i'm struggling to filter on a Matrix as have added a measure and only want certain records to show.

Add the 'Measure_Filter' to the Matrix as a Field and see how it populates. This helped me visualize this workaround. You should see all 1's. From there you could try to identify why it is not working in the Matrix. Without seeing a model or screenshot, my first thought is it needs adjusting due to a hiearchy?

MallikVuyala58
New Member

Really helpful . Thanks

Anonymous
Not applicable

Hi,
I want to use measure in the slicer. And that measure consist of another two measures. So, I am not able to acheive this.

Anonymous
Not applicable

Hi @yybi123 ,

 

Based on your description, you can so some steps as follows.

Here is my test table.

v-yuaj-msft_0-1616135482438.png

I create two measures as follows.

Difference = MAXX('Measure_Slicer_test',[D2]-[D1])
Status = IF(ISBLANK([Difference]),"Gray", IF([Difference]>0, "Green", IF([Difference]<0, "Red", "Amber")))
1. Create a calculated table.
Status = 

var x1=SUMMARIZE('Measure_Slicer_test',Measure_Slicer_test[Product],"Status",[Status])

return

SUMMARIZE(x1,[Status])

v-yuaj-msft_0-1616135592563.png

2. use "Status"[Status] to create a slicer.

v-yuaj-msft_1-1616135712903.png

3. create a measure, then drag it to the table visual of the original table and set the value as "1".

Measure_filter = 

var x1=SELECTEDVALUE('Status'[Status])

return

IF(ISBLANK(x1),1,IF([Status]=x1,1,0))

v-yuaj-msft_2-1616135828497.png

Result:

 031901.gif

Hope that's what you were looking for.

Best Regards,

Yuna

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @Anonymous ,

 

From calculated table [Status],
In case the orginial table have several month and business unit, how can we add a column to count how many product under each status, based on date and Business unit filter from Original table.

 

Drake0911_0-1704788912457.png

 

Great solution representing the inguinity of using the OOB PBI capablities. 


Thank you !

Hi Team,

 

If One date is fixed and another date is selected on slicer then substarct both dates. and create measure of Date Bucket.

How to create date bucket measure in slicer.

 

Thanks! I have a very similar problem. Did exactly the steps you described and it works for the respective page perfectly.

 

But in my PBI data model i have several pages with different visualizations. And, i do want to make this new slicer (green, red etc.) to be applied for all other pages as well, but it doesn't work at all. 

Any solutions for this?

Hope to hear from you.

 

Best,

The Challenger

Hi! 

I have the same issue. 

Did you find a solution? 

Thank you in advance 🙂

 

amitchandak
Super User
Super User

@yybi123 , You need to create an independent table with these measure values. and then use them in slicer with group(values)

refer example of binning and segmentation

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization
my Video: https://youtu.be/CuczXPj0N-k

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.