How to create a values range slicer (not a time range)?

[Be]

I am looking for a way to create a slicer that will put the values into a range. I came across this thread and thought it might be the way to solve this problem - but wanted to create another thread in case there was an easier way. 

http://community.powerbi.com/t5/Developer/Slicer-Sort-Issue/m-p/19800#U19800

I have odometer values for a vehicle dataset I am working from. I want to create a slicer that will allow a user to choose 0-10K odometer readings or 10K-20K odometer reading etc.  

What is the easiest way to create this slicer? I have limited knowledge of DAX and no knowledge of M formulas. 

Thank you very much for the help from this community! 

[fso · Accepted solution]

Thinking about it now... there's a much simpler solution.
Just edit your query (Get Data) and add a custom column with this formula:

Text.From(Number.RoundDown([actual odometer values]/5000,0)*5000/1000)
& "K - "
& Text.From(((Number.RoundDown([actual odometer values]/5000,0)*5000+5000)/1000))
& "K"

That will give you a table like this and you can simply filter on the new column

 

You can use the first approach I posted here if the ranges would not always contain 5000 but vary in steps... But otherwise i'D go for this second option. 🙂

[shzyincu]

Do you know how to provide SLICER component as custom range not depended upon the dataset?

I need to provide 2 years start and end range to SLICER component, also is it possible to get SLICER component value in the advanced editor?

[Be]

Wow! That is amazing, it's like magic! 🙂

What would you call that kind of formula? M Formula? R formula? Still learning and just want to know what you did to make the ranges appear? 

Only one other issue now - when I create the slicer for the range values, it is putting them in this order:

0K-5K

100K-105K

105K-110K

10K-15K

110K-115K

etc.

I think I've seen others complain about a similar issue when it comes to month order or date order. I don't know how to fix this though. Does anyone have an idea of how to fix the order of the numbers to be from smallest to largest??

Thank you again!!!

[Anonymous]

Hello, if you want to change the sort, you can add another column with th following formula :

Number.RoundDown([actual odometer values]/5000,0)*5000/1000)

After, with the range column you select the "Sort by Column" feature, and choose the newly create column. As the column is number formatted, it should answer you need.

By the way, don't forget to hide the sorting column from the client if you don't want to see it in your report.

Hope it helps

Sebastien

[Be]

Hi Sebastien,

Maybe I'm missing something, but I don't understand where the "sort by column" feature is. Could you possibly provide a screenshot?

Thanks again for the help. I truly appreciate it! 

[Anonymous]

Hi @Be,

   the "Sort by column" is here:

[Be]

Thank you @Anonymous for the screenshot.

@Anonymous - when I entered that column and then went to sort by it. This is the error I got:

 

I think this is the same thing this person discovered too. http://community.powerbi.com/t5/Developer/Slicer-Sort-Issue/m-p/19800#U19800 But I don't understand the solution they came up with. My dataset is HUGE, so I can't see creating a new column that is numbered. (Maybe I'm wrong). If that is what I need to do, I'm not sure how to write the M code for that column to make sure the values are truly in order. Any thoughts @fso, @Anonymous, @Anonymous?

[Anonymous]

Hello @Be,

The error occurs because you try to sort the "Odometer range" by the newly created column. As it displayed in the message for diffents values of odometer range (eg: 10000, 11000, 12000, 13000, 14000) you have a single value to sort (10).

This behavior is normal for this.

I believe, that you wanted to sort the Range column (10K - 15k, 15K-20K, ...) with the newly created column. I'm right? The odometer range column cannot be sorted with something which doesn't have the same granularity.

You still have two calculated columns (the Literal range column and the sorted range column)?

[Anonymous]

@Be,

To illustrate what I mentionned previously, you should have 3 columns just like here :

The First one came from your data. The second one is the result of the formula to get the range. The third one, used the formula I gave you for the sort.

After this, you change the Sort Column datatype from Text to Number. You choose the Range column and Sort it by the Sort Column.

After that, you should be able to get the correct sorted slicer just like this : 

Sébastien

[Be]

Success!! @Anonymous Thank you, thank you, thank you! I really appreciate you taking your time to show and explain this to me. It seems the critical step I was missing was changing the type from text to number.  Thank you again!

[jmbouffard]

Hi,

The solution discussed in this thread is great however it does not enable users to select any range they would like, for example they would not be able to select 2500-7500 km. As an alternative I was wondering if a generic slicer that would apply a filter on decimal values was available ? I'm thinking about something similar to the "Time Brush" or "Advanced Time Slicer" visuals, but that would support any decimal values instead of only Date/Time values.

Any recommendations ?

[sdjensen]

You could perhaps use the "Conditional Column" feature to create your ranges and a sorting column.

[jmbouffard]

That is exactly what I'm currently doing. This works for creating a pre defined range as shown in the previous messages from this thread but it does not enable the user to select his own range.

[fso]

Hi, if I understand your questions correctly, you have a table with values and want to filter clicking on ranges.
So that selecting "10K-20K" shows all values between 10k and 20k?

Try this:

- Create two tables. "odometer" with your actual values in it and "ranges" that contains a start value, end value and what you want to call that range.

- You do not need to create a connection between these tables
- Create the measure you want to calculate, for example

MyMeasure = COUNTROWS(odometer)

- Now you can create a measure that is filtered by the "ranges" table. Note that I have used < in the second filter criteria, so that your 0-5K range is actually 0-4,999 and 5k-10k is 5,000-9,999.

Final calculation =
CALCULATE (
    [MyMeasure],
    FILTER (
        odometer,
        odometer[actual odometer values] >= MIN ( ranges[start] )
            && odometer[actual odometer values] < MAX ( ranges[end] )
    )
)

- Create a slicer using ranges[text]
- In your visualization, use [Final calculation] for the values

[Leila]

Dear fso

I appreciate this briliant solution for defining ranges in Power BI.
I went all the steps as you suggested. However, to customize it to my problem ( Session Duration Rages which is generally the sames as this problem) I used 

MyMeasure= SUM(Session Duration)
OR
CALCULATE ( SUM(Session Duration) , .....

and I was wondering why you formulated MyMeasure with counting the rows of the table.

Thanks for your answer in advance,

Regards,

Leila