Forum Discussion

Be's avatar
Be
Advocate 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 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! 

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

15 Replies

  • fso's avatar
    fso
    Advocate II
    10 years ago

    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's avatar
      Leila
      Regular Visitor
      9 years ago

      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

  • fso's avatar
    fso
    Advocate II
    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. :-)

    • Be's avatar
      Be
      Advocate I
      10 years ago

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

    • shzyincu's avatar
      shzyincu
      Helper I
      9 years ago

      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?