cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KMcCarthy9
Helper III
Helper III

Slider Slicer based on Count Values

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!

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @KMcCarthy9 ,

Based on your description, you need to use what-if parameter first:

what if.pngparameter.png

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 )

visual control.png

About Top N and Bottom N, use the same control value and use the topN / bottomN filter:

topn.png

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.

View solution in original post

6 REPLIES 6
v-yingjl
Community Support
Community Support

Hi @KMcCarthy9 ,

Based on your description, you need to use what-if parameter first:

what if.pngparameter.png

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 )

visual control.png

About Top N and Bottom N, use the same control value and use the topN / bottomN filter:

topn.png

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.

View solution in original post

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

KMcCarthy9_0-1600198678051.png

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

KMcCarthy9_1-1600198776545.png

 

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! 

amitchandak
Super User IV
Super User IV

@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

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

darentengmfs
Super User I
Super User I

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.