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
KMcCarthy9
Helper IV
Helper IV

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.

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

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

darentengmfs
Post Prodigy
Post Prodigy

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