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

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