Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jessicarocha
Helper IV
Helper IV

Can I create a filter containing a measure by interval?

I have a bar graph that shows the revenue per customer. In this page, I have several slices. Thus, the user can filter per customer, per market, per fiscal year or business type.
In addition, he can click in the bar and drill through for more detailed customer information.

The problem is that for small values of revenue, I have no bar. Thus, the user can not drill through for more customer information.

I was thinking that maybe if I was able to add a measure slicer, like: show me customers with revenue from 10 to 20 millions only, the axis would adjust automatically, allowing the user could to see the bar and drill through for more customer details. 

I would like to make it easy for the user to filter, to create intervals such as:

Example of Filter Revenue:

 

0 - 5 Millions

5 Millions - 10 Millions 

10 Millions - 20 Millions

>20 Millions

 

However, I don't know how to do that. My current slicer is the default one. 

jessicarocha_4-1632464667595.png

 

jessicarocha_2-1632464080541.png

 

 

jessicarocha_3-1632464229413.png

 

 

If you have a different idea on how to solve this, how to make the drill through appear, I would also be grateful. 

 

Thanks in advance!

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @jessicarocha ;

You could enter a table ,then create a flag measure.

1.enter table as slicer.

vyalanwumsft_0-1632724673614.png

2.create a flag measure.

flag =
VAR _flag =
    SWITCH (
        MAX ( 'Slicer'[Filter] ),
        "0 - 5 Millions",
            IF ( SUM ( 'Table'[Revenue] ) >= 0 && SUM ( 'Table'[Revenue] ) < 5000000, 1 ),
        "5 Millions - 10 Millions",
            IF (
                SUM ( 'Table'[Revenue] ) >= 5000000
                    && SUM ( 'Table'[Revenue] ) < 10000000,
                1
            ),
        "10 Millions - 20 Millions",
            IF (
                SUM ( 'Table'[Revenue] ) >= 10000000
                    && SUM ( 'Table'[Revenue] ) < 20000000,
                1
            ),
        ">20 Millions", IF ( SUM ( 'Table'[Revenue] ) >= 20000000, 1 )
    )
RETURN
    IF ( ISFILTERED ( Slicer ), _flag, 1 )

3.apply flag measure into bar visual.

vyalanwumsft_1-1632724891829.png

The final output is shown below:

vyalanwumsft_4-1632724949452.pngvyalanwumsft_5-1632724968565.png

Best Regards,
Community Support Team_ Yalan Wu
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

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @jessicarocha ;

Hello, you need to check whether the fields in this Slicer table are consistent with the fields in the Flag Measure. Pay special attention to the Spaces, such as the front and back Spaces

vyalanwumsft_0-1632811668517.pngvyalanwumsft_1-1632811720030.png

Sometimes the space is not easy to find, you can use another auxiliary measure to verify whether the consistency, such as:

check =IF ( MAX ( 'Slicer'[Filter] ) = "0 - 5 Millions", 1, 0 )

then put it into card and select the "0 - 5 Millions", if card is always show "0", Then you can determine that the field does not match and you need to modify it.  if not right , can you share the slicer table screenshot?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-yalanwu-msft thank you so much. That was exactly my problem. All fixed now. 

 

I really appreciate the help!

 

Kind regards, 

Jéssica

v-yalanwu-msft
Community Support
Community Support

Hi, @jessicarocha ;

You could enter a table ,then create a flag measure.

1.enter table as slicer.

vyalanwumsft_0-1632724673614.png

2.create a flag measure.

flag =
VAR _flag =
    SWITCH (
        MAX ( 'Slicer'[Filter] ),
        "0 - 5 Millions",
            IF ( SUM ( 'Table'[Revenue] ) >= 0 && SUM ( 'Table'[Revenue] ) < 5000000, 1 ),
        "5 Millions - 10 Millions",
            IF (
                SUM ( 'Table'[Revenue] ) >= 5000000
                    && SUM ( 'Table'[Revenue] ) < 10000000,
                1
            ),
        "10 Millions - 20 Millions",
            IF (
                SUM ( 'Table'[Revenue] ) >= 10000000
                    && SUM ( 'Table'[Revenue] ) < 20000000,
                1
            ),
        ">20 Millions", IF ( SUM ( 'Table'[Revenue] ) >= 20000000, 1 )
    )
RETURN
    IF ( ISFILTERED ( Slicer ), _flag, 1 )

3.apply flag measure into bar visual.

vyalanwumsft_1-1632724891829.png

The final output is shown below:

vyalanwumsft_4-1632724949452.pngvyalanwumsft_5-1632724968565.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yalanwu-msft thank you so much for the detailed answer and the pbix file. This is exactly what I am trying to achieve. 

I followed your steps but something is not right for me. Only one option in the slicer is working (>20 Millions). When I select any other, no data appears. I can't figure it out what is wrong. Do you have any idea?

 

Best regards, 

Jéssica

jessicarocha_0-1632810886708.png

 

jessicarocha_1-1632810900859.png

 

jessicarocha_2-1632810965828.png

 

jessicarocha_3-1632811020661.png

 



 

amitchandak
Super User
Super User

@jessicarocha , You need to binning or segmentation using an independent table

 

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...


Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

 

Binning

 

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.