Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a profit and margin table where the margin is a measure. I want to create a slicere where I can select 1 of 2 options: "Positive Margin" or "Negative Margin" where positive margin returns all Quarters where Margins that are greater than 0 and Negative Margin returns all quarters where margins are less than 0.
Solved! Go to Solution.
Click here to download solution
How is works ...
Create a option pick list table and add a single selection slicer
Create a measure that returns 1 when the output matches the slicer option
IsOption =
VAR selectedoption = SELECTEDVALUE('Option picklist'[Option])
RETURN
SWITCH(TRUE(),
selectedoption = "All Margins",1,
selectedoption = "Positive Margins" && [Margin%] >= 0,1,
selectedoption = "Negative Margins" && [Margin%] < 0,1
)
Add a filter must = 1 to your repport visual
Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
Please now click the thumbs up and the [accept as solution] button. Thnak you.
One question per ticket please!
If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
Please now click the thumbs up and the [accept as solution] button. Thank you.
Hi @DJSwezey ,
You can create a separate tab;e with no any relationship with the categories you want to show in slicer and create a measure with switch function or if else with the logic that you explained above.
Click here to download solution
How is works ...
Create a option pick list table and add a single selection slicer
Create a measure that returns 1 when the output matches the slicer option
IsOption =
VAR selectedoption = SELECTEDVALUE('Option picklist'[Option])
RETURN
SWITCH(TRUE(),
selectedoption = "All Margins",1,
selectedoption = "Positive Margins" && [Margin%] >= 0,1,
selectedoption = "Negative Margins" && [Margin%] < 0,1
)
Add a filter must = 1 to your repport visual
Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos.
One question per ticket please. If you need to extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !
If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
Please now click the thumbs up and the [accept as solution] button. Thnak you.
Perfect! This worked on the most granular level of my table, but not sure it works if I drill up.
You can drill down quarter into state and then into city.
When I filter by "Positive" or 'negative" at this level things work great.
If I drill up then it looks like the filter doesnt work
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |