Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to created a dynamic slicer on the page where the user can adjust the range of values they want to see. My issue is that the field is technically a text field, that I normally use as a count, however the count option is not available on the slicer visual. I also created a measure to get a numeric field, but you can't use measures in slicers.
Ultimately I want to show on the report a slider, that users can adjust the number of workorders to see data for vendors with over X amount of work orders. Addiitionally, I would like to show Top/Bottom X number also incorporating the number of work orders.
Users typically want to either look at
1. all data for vendors that have over X number of work orders
or
2. Top X number of vendors that have at least X number of work orders.
I've done some research, but I can't seem to figure this out.
I have posted a very slimmed down example .pbix file here: .pbix
Thank you for any help!
Solved! Go to Solution.
Hi @KMcCarthy9 ,
Based on your description, you need to use what-if parameter first:
You can change the min and max value as your needed at any time. It will automatically generate a slider slicer
Then create this control measure, put it in the visual filter and set its value as 1:
Control =
VAR _min =
CALCULATE ( MIN ( 'Parameter'[Parameter] ), ALLSELECTED ( Parameter ) )
VAR _max =
CALCULATE ( MAX ( 'Parameter'[Parameter] ), ALLSELECTED ( Parameter ) )
VAR _count =
CALCULATE (
COUNT ( data[WorkOrder Number] ),
ALLEXCEPT ( data, data[Work Order Vendor] )
)
RETURN
IF ( _count >= _min && _count <= _max, 1, 0 )
About Top N and Bottom N, use the same control value and use the topN / bottomN filter:
Attached the sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi @KMcCarthy9 ,
Based on your description, you need to use what-if parameter first:
You can change the min and max value as your needed at any time. It will automatically generate a slider slicer
Then create this control measure, put it in the visual filter and set its value as 1:
Control =
VAR _min =
CALCULATE ( MIN ( 'Parameter'[Parameter] ), ALLSELECTED ( Parameter ) )
VAR _max =
CALCULATE ( MAX ( 'Parameter'[Parameter] ), ALLSELECTED ( Parameter ) )
VAR _count =
CALCULATE (
COUNT ( data[WorkOrder Number] ),
ALLEXCEPT ( data, data[Work Order Vendor] )
)
RETURN
IF ( _count >= _min && _count <= _max, 1, 0 )
About Top N and Bottom N, use the same control value and use the topN / bottomN filter:
Attached the sample file in the below, hopes to help you.
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
@v-yingjl I am having trouble getting the Top/Bottom N to work correctly. In your sample .pbix it is working, but in my original report I keep running into issues that I can't pinpoint.
I am looking for the Top/Bottom N of vendors based on MTTR Days (lower the better). I switched out the workorder count for my MTTR Days column, but whenever I try to use the parameter slider with it, the visual goes blank. Anything off the top of your head that I am doing wrong?
Ultimately looking for a user to use the parameter slider to set a range regarding workorder volume for each vendor, and then see the Top/Bottom N vendors based on MTTR Days.
The Top/Bottom N seems to be working when the slider is in full range (including everything), but then if I set min to 10, visual is blank???
Thanks!
@v-yingjl Actuall in your test file it does not seem to be what I want it to be doign either.
If you select the Bottom 5 according to MTTR Days it gives you this result:
But if I then use the parameter slider to at least 10 work orders, it only gives me the Bottom 4 results, when it should still give me 5 by replacing a vendor that doesn't meet the criteria with one that does (at least 10 workorders).
How do I get this to work where it is dynamic and the user can choose Top N based on MTTR Days with at least X many of workorders. Thanks!
@v-yingjl Thank you so much! I'm still playing around with the Top/Bottom N, but I got the slider slicer working perfectly. The DAX was not as complicated as I originally thought it would have to be. Thanks for the help!
@KMcCarthy9 , to make a slicer work on measure you need a row context.
example to filter rank forced context using values
City Rank = RANKX(all(Geography[City]),[Sales])
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )
Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]<=10),[Sales] )
in place this 2 or 10 you can use what if parameter
or Refer this example of Rank
Hi @KMcCarthy9
What if you duplicate the column and change the data type to Integer using Power Query? And create the slicer based on the new column?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |