cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jessicarocha
Helper III
Helper III

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.

View solution in original post

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.