cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Create a slicer thats groups measure values into ranges/groupings

Hi there,

 

Is it possible to create a slicer that has value ranges/groupings that filter tables/visuals based on if the measures fall into that range/group?

 

I've created the following DAX table as my slicer options;

OptionKey
<100,0001
100,000 - 1,000,0002
1,000,000 - 10,000,0003
>10,000,0004

 

And I have a table of total sales by categories;

CategoryTotal Sales
A90000
B50000
C400000
D600000
E2000000
F7000000
G12000000

 

Total Sales is a measure field that is made up of SUMX(SUMMARIZE(FILTER(sales <> 0), category), price * volume).

 

What I want is to be able to select a slicer option and have it filter the table to the range selected.

 

I've tried using IF, SIWTCH, SELECTEDVALUE and tried a few similar questions/solutions that I have found on here but nothing seems to work.

 

Any help would be great.

 

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Create a slicer thats groups measure values into ranges/groupings

Hi @shsx ,

You can create this measure, set its values as 1 and put it in the table visual filter:

Control = 
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( Slicer ),
        "Min",
            SWITCH (
                [Option],
                "<100000", 0,
                "100000 - 1000000", 100000,
                "1000000 - 10000000", 1000000,
                ">10000000", 10000000
            ),
        "Max",
            SWITCH (
                [Option],
                "<100000", 100000,
                "100000 - 1000000", 1000000,
                "1000000 - 10000000", 10000000,
                ">10000000", 10000000000
            )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( tab, [Total Sales] >= [Min] && [Total Sales] < [Max] ) ) > 0,
        1,
        0
    )

table slicer.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Create a slicer thats groups measure values into ranges/groupings

@shsx - You could accomplish this with a disconnected table for your slicer. Basically the core idea is to switch how you perform a calculation based upon a slicer selection. Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Create a slicer thats groups measure values into ranges/groupings

Thanks both, I've looked over all the pages and nothing has seemed to work as I want it to. The disconnected tables has been the closest but I have more than 2 values, and when I have been able to kinda of make it work, it only works when 1 value is selected for slicing. If I want 2 options then it doesn't work.


Any other thoughts?

Highlighted
Community Support
Community Support

Re: Create a slicer thats groups measure values into ranges/groupings

Hi @shsx ,

You can create this measure, set its values as 1 and put it in the table visual filter:

Control = 
VAR tab =
    ADDCOLUMNS (
        DISTINCT ( Slicer ),
        "Min",
            SWITCH (
                [Option],
                "<100000", 0,
                "100000 - 1000000", 100000,
                "1000000 - 10000000", 1000000,
                ">10000000", 10000000
            ),
        "Max",
            SWITCH (
                [Option],
                "<100000", 100000,
                "100000 - 1000000", 1000000,
                "1000000 - 10000000", 10000000,
                ">10000000", 10000000000
            )
    )
RETURN
    IF (
        COUNTROWS ( FILTER ( tab, [Total Sales] >= [Min] && [Total Sales] < [Max] ) ) > 0,
        1,
        0
    )

table slicer.png

Attached a sample file in the below, hopes to help you.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors