Create a slicer thats groups measure values into ranges/groupings

[Anonymous]

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;

Option	Key
<100,000	1
100,000 - 1,000,000	2
1,000,000 - 10,000,000	3
>10,000,000	4

And I have a table of total sales by categories;

Category	Total Sales
A	90000
B	50000
C	400000
D	600000
E	2000000
F	7000000
G	12000000

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

[v-yingjl · Accepted solution]

Hi @Anonymous ,

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
    )

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.

[Greg_Deckler]

@Anonymous - 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/ba-p/279563

[Anonymous]

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?

[amitchandak]

@Anonymous , you need dynamic segmentation of binning

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins
https://www.credera.com/blog/technology-solutions/creating-aging-report-using-a-user-selected-date-in-power-bi/

https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/