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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gandalf1969
Frequent Visitor

Use Range Slicer to filter and display only values outside the selected range.

I have a table containing sales transactions including the Gross Margin % for each.  I want to give users the ability to use a range slicer to filter out "normal" transactions where we made x% Gross Margin (eg: between 25% and 75%) and only display the transactions that fall outside that range (eg: anything below the low threshold (eg: 25%)  and above the high threshold (eg: above 75%) highlighted below). 

 

I want the users to be able to select the upper and lower limits for themselves between 1-100% using a range-slicer.  Ideally the range would be in 5% increments and not litterally 1%-100% but I'm not sure I can do that with a range slicer?

   

Screenshot 2023-10-24 at 4.45.21 PM.png

Thanks in advance for any input.

G

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @gandalf1969 ,

 

Here are the steps you can follow:

1. New parameter – Numeric range.

vyangliumsft_0-1698297946056.png

Data type – Decimal number

Minium – 0.01

Maximum -- 1

vyangliumsft_1-1698297946060.png

Refer to:

Use parameters to visualize variables - Power BI | Microsoft Learn

Column tools -- %

vyangliumsft_2-1698297968314.png

Slicer settings – Style – Between.

vyangliumsft_3-1698297968317.png

2. Create measure.

Flag =
var _min=MINX(ALLSELECTED(Parameter),'Parameter'[Parameter])
var _max=MAXX(ALLSELECTED(Parameter),'Parameter'[Parameter])
return
IF(
    [GM%]>=_min&&[GM%]<=_max,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_4-1698297986344.png

4. Result:

vyangliumsft_5-1698297986346.png

 

Best Regards,

Liu Yang

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

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @gandalf1969 ,

 

Here are the steps you can follow:

1. New parameter – Numeric range.

vyangliumsft_0-1698297946056.png

Data type – Decimal number

Minium – 0.01

Maximum -- 1

vyangliumsft_1-1698297946060.png

Refer to:

Use parameters to visualize variables - Power BI | Microsoft Learn

Column tools -- %

vyangliumsft_2-1698297968314.png

Slicer settings – Style – Between.

vyangliumsft_3-1698297968317.png

2. Create measure.

Flag =
var _min=MINX(ALLSELECTED(Parameter),'Parameter'[Parameter])
var _max=MAXX(ALLSELECTED(Parameter),'Parameter'[Parameter])
return
IF(
    [GM%]>=_min&&[GM%]<=_max,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_4-1698297986344.png

4. Result:

vyangliumsft_5-1698297986346.png

 

Best Regards,

Liu Yang

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

Thank you for your ideas.  The output is actually the opposite of what I am wanting to acheive BUT I can see how changing the Visual Filter to '0' does the trick.  I can see how a simple measure on a disconnected table allows me to set the min/max.  I'm not sure Parameters acheives more than just using a slicer in this instance (note your PBIX demonstrates the range is contiguous values regardless of your incremental parameters).  I DO see how it lets you SET incremental values but as soon as I try to use a range slicer, it turns it into a contiguous range from min-to-max.  So, I will accept this as the answer because it does inadvertantly answer my questions and I can build a solution.

 

Use a disconnected table of my incremental Min/Max values.

Use separate Min & Max dropdown slicers on the table to select the values.

Use a measure to create the filter based on those Min/Max values for in/out of range.

Apply that measure against the data table - using a visual filter to show only flag'd values of '0'.

It seems using a range slicer does not allow for incremental values.

 

Thank you,

G

lbendlin
Super User
Super User

You could use a disconnected range slicer and a "not"  measure as your visual filter.  Or you could use the filter pane which would allow you to specify two conditions, namely "Margin less than 25% OR Margin greater than 75%".

Thank you for your response.  I'm looking for a solution that gives the user a control (slicer) on the page to dynamically filter the data (vs having them use the filter pane manually).

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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