topic Re: How to create a Slicer based on a range of Calculated Measure in DAX Commands and Tips

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

Anonymous — Tue, 10 Sep 2019 06:45:26 GMT

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

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

sturlaws — Tue, 10 Sep 2019 11:37:45 GMT

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

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

Anonymous — Wed, 11 Sep 2019 00:21:06 GMT

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 thinks

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

sturlaws — Wed, 11 Sep 2019 07:49:13 GMT

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.

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

malcolmwunwl — Wed, 11 Sep 2019 09:34:15 GMT

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),

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

malcolmwunwl — Wed, 11 Sep 2019 09:42:55 GMT

"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

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

sturlaws — Wed, 11 Sep 2019 11:08:39 GMT

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
    )
)

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

Anonymous — Wed, 11 Sep 2019 14:03:04 GMT

will try in morning

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

COUNTROWS ( '1Calculation' ),

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

sturlaws — Wed, 11 Sep 2019 16:01:12 GMT

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])

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

malcolmwunwl — Thu, 12 Sep 2019 08:37:50 GMT

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))

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

sturlaws — Thu, 12 Sep 2019 22:27:34 GMT

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
)

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

Anonymous — Fri, 13 Sep 2019 12:22:18 GMT

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=eyJrIjoiNzc5YTU3MjUtMjY0OS00NGEzLWIxYzEtOTNlYjA0ZmVkMTQ2IiwidCI6IjdlNDk4MjVlLWQxODAtNDMxNS1iNTZlLTNmYmUzZDliYThiZSJ9

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)

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

sturlaws — Fri, 13 Sep 2019 13:23:04 GMT

[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

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

Anonymous — Sat, 14 Sep 2019 00:25:18 GMT

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

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

rubymaya — Mon, 07 Dec 2020 04:12:46 GMT

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

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

rubymaya — Mon, 07 Dec 2020 06:36:28 GMT

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.

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

rubymaya — Mon, 07 Dec 2020 09:10:32 GMT

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 🙂