Forum Discussion
How to create a values range slicer (not a time range)?
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. :-)
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?
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)?
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
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!