cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
malcolmwun
Resolver II
Resolver II

How to create a Slicer based on a range of Calculated Measure

Hi ,  like a slicer that has a range of value, where upon selcting, it will pick a Measure that falls within the range. I tried going thru many examples in the internet but have not come a cross one that address my challenge. 

 

Imaging I have 2 tables (i) Country GDP (ii) Country Defence spending .. I create a measure of DefenceSpending / Country GDP = X%

Using the attached file, the end result if to be able to show the following when I select the slicer 

 

What I am looking for is to select the slicer below and PowerBI table return only Country with measure within the selected range

I have created a new table (without relationship), with the below 4 scenarios BUT i dont know how to make the slicer full the data I wanted 

 

SLICER
0% < 1.0% This should select Myamar, Malaysia, Pakistan, Brunei, Korea
1.0% < 2.0% This should select Philippine, Nepal, Bangladesh, Vietnam, New Zealand
2.0% < 3.0% This should select Sri Lanka, Japan, HongKong
3% or greater This should select Singapore, Thailand, China Taiwan, Indonesia & Australia

PowerBI Table after creating a measure.JPG

TableDefencespend.JPGTableGDP.JPG

 

 

 

2 ACCEPTED SOLUTIONS

[filterMeasure] is only meant to do just that, it filters the data based on your slicer selection, and returns the number of rows which satisfy the current filter context.

 

Since you have not posted any data or pbix-file, I have scraped together some data which resembles your data here:
GDP vs Military expenditure

 

Since I don't know how your data look like, you might have to consider the relationship between your tables. And also possibly count the 'Military expenditure'-table instead of the GDP-table

View solution in original post

This is brillaint,, i replicatre what you did and it works  ,,,,,, so great ,,, i see how u use visual filter, count row to amke this work...


@sturlaws wrote:

[filterMeasure] is only meant to do just that, it filters the data based on your slicer selection, and returns the number of rows which satisfy the current filter context.

 

Since you have not posted any data or pbix-file, I have scraped together some data which resembles your data here:
GDP vs Military expenditure

 

Since I don't know how your data look like, you might have to consider the relationship between your tables. And also possibly count the 'Military expenditure'-table instead of the GDP-table


 

View solution in original post

16 REPLIES 16
rubymaya
Helper II
Helper II

@sturlaws  found the problem, I didnt put the filter to visuals I want to slice... worth to mention here that we need to filter the related visuals to call out the filterMeasure ----> filterMeasure is greater than 0, after that everything works.

 

Thanks for this excellent sample, you saved my day 🙂

sturlaws
Super User
Super User

Hi,


create a slicer like this:

filterMeasure =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Intervals[Interval] ) = "0% < 1.0%"; CALCULATE (
        COUNTROWS ( GDP );
        FILTER ( GDP; [Defence vs GDP] >= 0 && [Defence vs GDP] < 1 )
    );
    SELECTEDVALUE ( Intervals[Interval] ) = "1.0% < 2.0%"; CALCULATE (
        COUNTROWS ( GDP );
        FILTER ( GDP; [Defence vs GDP] >= 1 && [Defence vs GDP] < 2 )
    );
    SELECTEDVALUE ( Intervals[Interval] ) = "2.0% < 3.0%"; CALCULATE (
        COUNTROWS ( GDP );
        FILTER ( GDP; [Defence vs GDP] >= 2 && [Defence vs GDP] < 3 )
    );
    SELECTEDVALUE ( Intervals[Interval] ) = "3.0% <"; CALCULATE ( COUNTROWS ( GDP ); FILTER ( GDP; [Defence vs GDP] >= 3 ) );
    0
)

and add this measure to the filter part of you visual

cheers,
S

I have tried this on my project (which have same logic).. However I got an error .. Can you help me identify and resolve the error I am getting at the bottom "calculate function is required".. Many thinksCaptureerror.JPG

your calculate statements looks like this:

CALCULATE (
    COUNTROWS ( '1Calculation' ),
    [Services Connect Rate (NWS)],
    FILTER (
        '1Calculation',
        [Services Connect Rate (NWS)] > -1000
            && [Services Connect Rate (NWS)] < 0.01
    )
) 

After the COUNTROWS-function, you reference [Services Connect Rate (NWS)] which makes no sense. What are you trying to achive with this reference?

Next time, could post the code instead of a snapshot? It makes helping you much easier if I don't have to decipher a unclear image first.

Start off with an existing Measure I created some time ago  which will return a XX%

Services Connect Rate (NWS) = DIVIDE([DBF+UU+U (NWS)],[DBF(ATU)])

 
With your help, I am hoping to create the Slicer Measure using your guide
 
CR Measure = SWITCH ( TRUE (),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.0%<1.0",CALCULATE(COUNTROWS('1Calculation'),[Services Connect Rate (NWS)],FILTER('1Calculation',[Services Connect Rate (NWS)]>-1000 && [Services Connect Rate (NWS)] < .01)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.1%<2.0",CALCULATE(COUNTROWS('1Calculation'),[Services Connect Rate (NWS)],FILTER('1Calculation',[Services Connect Rate (NWS)]>=.01 && [Services Connect Rate (NWS)] < .02)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.2%<3.0",CALCULATE(COUNTROWS('1Calculation'),[Services Connect Rate (NWS)],FILTER('1Calculation',[Services Connect Rate (NWS)]>=.02 && [Services Connect Rate (NWS)] < .03)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.3%<4.0",CALCULATE(COUNTROWS('1Calculation'),[Services Connect Rate (NWS)],FILTER('1Calculation',[Services Connect Rate (NWS)]>=.03 && [Services Connect Rate (NWS)] < .04)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.4%<5.0",CALCULATE(COUNTROWS('1Calculation'),[Services Connect Rate (NWS)],FILTER('1Calculation',[Services Connect Rate (NWS)]>=.04 && [Services Connect Rate (NWS)] < .05)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="5.0%+",CALCULATE(COUNTROWS('1Calculation'),[Services Connect Rate (NWS)],FILTER('1Calculation',[Services Connect Rate (NWS)]>=.05),
 
 
 
 
 

"After the COUNTROWS-function, you reference [Services Connect Rate (NWS)] which makes no sense. What are you trying to achive with this reference?"

 

Reply, If I select from the slicer, "1.0%<2.0", I want the Table to show the attach Cicrle in BLUE

Capture.JPGed

The calculate-function takes these arguments:

CALCULATE(<expression>,<filter1>,<filter2>…)

and the way you have written you code you are using the measure [Services Connect Rate (NWS)] as a filter. But a measure by itself like this is not a valid filter argument. Try writting your code like this:

SELECTEDVALUE ( SlicerNWS[CR NWS Range] ) = "0.0%<1.0", CALCULATE (
    COUNTROWS ( '1Calculation' ),
    FILTER (
        '1Calculation',
        [Services Connect Rate (NWS)] > -1000
        && [Services Connect Rate (NWS)] < .01
    )
)

will try in morning

 

by the way, what is the role of the countrow is making this dAX work

COUNTROWS ( '1Calculation' ),

 

The first argument to calculate is a tabel. When using the calculate function it changes the context of how that table is filtered. Countrows counts the rows of the table after it has been filtered. But it not just the filter-function that filters the table, slicers and axis in a report also adds to how the table is filtered.

I used countrows since i don't know how your data looks like. You could use distinct(table[customer id]) instead, or sum(table[sales])

Hi I still get an error after using this DAX 

CR Measure = SWITCH ( TRUE (),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.0%<1.0",CALCULATE(COUNTROWS('1Calculation'),FILTER('1Calculation',[Services Connect Rate (NWS)]>-1000 && [Services Connect Rate (NWS)] < .01)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.1%<2.0",CALCULATE(COUNTROWS('1Calculation'),FILTER('1Calculation',[Services Connect Rate (NWS)]>=.01 && [Services Connect Rate (NWS)] < .02)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.2%<3.0",CALCULATE(COUNTROWS('1Calculation'),FILTER('1Calculation',[Services Connect Rate (NWS)]>=.02 && [Services Connect Rate (NWS)] < .03)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.3%<4.0",CALCULATE(COUNTROWS('1Calculation'),FILTER('1Calculation',[Services Connect Rate (NWS)]>=.03 && [Services Connect Rate (NWS)] < .04)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="0.4%<5.0",CALCULATE(COUNTROWS('1Calculation'),FILTER('1Calculation',[Services Connect Rate (NWS)]>=.04 && [Services Connect Rate (NWS)] < .05)),
SELECTEDVALUE(SlicerNWS[CR NWS Range])="5.0%+",CALCULATE(COUNTROWS('1Calculation'),FILTER('1Calculation',[Services Connect Rate (NWS)]>=.05),
0))Capture.JPGCapture2.JPG

I think you might have misplaced a paranthesis. Your last case statment and else-value looks like this

SELECTEDVALUE ( SlicerNWS[CR NWS Range] ) = "5.0%+", CALCULATE (
        COUNTROWS ( '1Calculation' ),
        FILTER ( '1Calculation', [Services Connect Rate (NWS)] >= 0.05 ),
    0
))

when it should have been like this

SELECTEDVALUE ( SlicerNWS[CR NWS Range] ) = "5.0%+", CALCULATE (
        COUNTROWS ( '1Calculation' ),
        FILTER ( '1Calculation', [Services Connect Rate (NWS)] >= 0.05 )
    ),
    0
)

Formula seem acceptable, however the look is what i am hoping for.. a web version is publish.. I am using GDP vs Defense spend example instead... https://app.powerbi.com/view?r=eyJrIjoiNzc5YTU3MjUtMjY0OS00NGEzLWIxYzEtOTNlYjA0ZmVkMTQ2IiwidCI6IjdlN...

 

i the screen shot, the filter is selecting the correct country in the chart BUT, the value incorrectly showing 100.00%

DAX

FilterMeasure =
SWITCH (
    TRUE (),
SELECTEDVALUE ('what we want to achieve'[SLICER]) = "0.0%<1.0%", CALCULATE (COUNTROWS (GDP), FILTER ( GDP,[Defense%] >= 0 && [Defense%] < 0.01 )),
SELECTEDVALUE ('what we want to achieve'[SLICER]) = "1.0%<2.0%", CALCULATE (COUNTROWS (GDP), FILTER ( GDP,[Defense%] >= 0.01 && [Defense%] < 0.02 )),
SELECTEDVALUE ('what we want to achieve'[SLICER]) = "2.0%<3.0%", CALCULATE (COUNTROWS (GDP), FILTER ( GDP,[Defense%] >= 0.02 && [Defense%] < 0.03 )),
SELECTEDVALUE ('what we want to achieve'[SLICER]) = "3.0%+", CALCULATE (COUNTROWS (GDP), FILTER ( GDP,[Defense%] >= 0.03)
    ),0)

 

Capture.JPG

[filterMeasure] is only meant to do just that, it filters the data based on your slicer selection, and returns the number of rows which satisfy the current filter context.

 

Since you have not posted any data or pbix-file, I have scraped together some data which resembles your data here:
GDP vs Military expenditure

 

Since I don't know how your data look like, you might have to consider the relationship between your tables. And also possibly count the 'Military expenditure'-table instead of the GDP-table

This is brillaint,, i replicatre what you did and it works  ,,,,,, so great ,,, i see how u use visual filter, count row to amke this work...


@sturlaws wrote:

[filterMeasure] is only meant to do just that, it filters the data based on your slicer selection, and returns the number of rows which satisfy the current filter context.

 

Since you have not posted any data or pbix-file, I have scraped together some data which resembles your data here:
GDP vs Military expenditure

 

Since I don't know how your data look like, you might have to consider the relationship between your tables. And also possibly count the 'Military expenditure'-table instead of the GDP-table


 

hi, what setting you use for visuals that needs to use the filterMeasure, 

eg, if i add 1 visual with the exact axis, using your pbix file, it didnt filter like the rests of the visuals.

Must be some setting i missed out for the new visual

Hi @sturlaws ,

see the attached from the sample pbix, the visual i just added(with exact axis), doesnt filter correctly. (highlighted)

unless if i copy the visual directly, then the filter follows. That's what i meant earlier, there must be some step or setting i  missed to make it works automatically with the filterMeasure created.Capture.JPG

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors