Forum Discussion

Be's avatar
Be
Icon for Advocate I rankAdvocate I
10 years ago
Solved

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

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 ...
Need Help
Tutorial Requests
  • fso's avatar
    fso
    10 years ago

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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
Be
Icon for Advocate I rankAdvocate I
10 years ago

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's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi Be,

     

       the "Sort by column" is here:

     

    • Be's avatar
      Be
      Icon for Advocate I rankAdvocate I
      10 years ago

      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's avatar
        Anonymous
        Not applicable
        10 years ago

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