cancel
Showing results for
Did you mean:
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.

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

1 ACCEPTED SOLUTION
Community Support

Hi, @jessicarocha ;

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

1.enter table as slicer.

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.

The final output is shown below:

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.

5 REPLIES 5
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

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.

Helper III

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

I really appreciate the help!

Kind regards,

Jéssica

Community Support

Hi, @jessicarocha ;

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

1.enter table as slicer.

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.

The final output is shown below:

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.

Helper III

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

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

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!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!