Create a slicer with a Mesure

[LD1]

Hi everyone !

I'd like to create a slicer segment with a “rate” (measure) that lets you choose a rate range.
For example: Here I can select rate from 0% to 73% but it can be 5% to 13% also. 

I know it's easily possible in Tableau but Power BI it seems no. 

Can you give me a tips in DAX to do that please ? 

You need to know, I don't manage the dataset (power query) because it managing by IT department. 

Thank you a lot and have a good day, 

LD

[techies · Accepted solution]

Hi @LD1 i guess you can create a disconnected table like this for label slicer

RateRanges =

DATATABLE (

    "Label", STRING,

    "MinRate", DOUBLE,

    "MaxRate", DOUBLE,

    {

        { "0% - 10%", 0.00, 0.10 },

        { "10% - 20%", 0.10, 0.20 },

        { "20% - 30%", 0.20, 0.30 },

        { "30% - 40%", 0.30, 0.40 },

        { "40% - 50%", 0.40, 0.50 },

        { "50% - 60%", 0.50, 0.60 },

        { "60% - 70%", 0.60, 0.70 },

        { "70% - 80%", 0.70, 0.80 },

        { "80% - 90%", 0.80, 0.90 },

        { "90% - 100%", 0.90, 1.00 }

    }

)

And once your Rate measure is in place, then create this dax measure

IsInSelectedRateRange =

VAR SelectedMin = MIN(RateRanges[MinRate])

VAR SelectedMax = MAX(RateRanges[MaxRate])

VAR CurrentRate = [Your rate measure]

RETURN

    IF (

        CurrentRate >= SelectedMin && CurrentRate <= SelectedMax,

        1,

        0

    )

Then use the IsInSelectedRateRange measure in your final measure like this

Filtered Value =

IF (

    [IsInSelectedRateRange] = 1,

    SELECTEDVALUE(sales[Value]),

    BLANK()

)

[Nasif_Azam · Accepted solution]

Hey @LD1 ,

To create a slicer for selecting a rate range in Power BI based on a measure, you can follow these steps:

1. Create a Measure

You need to create a DAX measure to represent the rate. For example, if you have a column named RateColumn in your dataset, you can create a measure like this:

RateMeasure = AVERAGE('Table'[RateColumn])

This measure will calculate the average rate for your data.

2. Create a Supporting Table for the Slicer

You will need a table to use in the slicer for selecting the range. This table will contain the possible range values, like this:

RateRange = GENERATESERIES(0, 100, 5)

This creates a table with values from 0 to 100, in steps of 5. You can adjust the range and step size to fit your needs.

3. Add a Slicer to the Report

Once you have the RateRange table, add it to the slicer visualization. You can add this table to your report and use it for the slicer selection.

4. Create a Filter Measure Based on Slicer Selection

Now you can create a measure that will filter the data based on the selected rate range. This measure will use the slicer value to filter your data. For example:

FilteredRate = 
CALCULATE(
    [RateMeasure],
    FILTER(
        'Table',
        'Table'[RateColumn] >= MIN('RateRange'[Value]) && 'Table'[RateColumn] <= MAX('RateRange'[Value])
    )
)

This DAX formula will filter your data based on the slicer selection and calculate the rate within the selected range.

5. Use the Measure in Visuals

Finally, use the FilteredRate measure in your visuals, and the slicer will control the range that is displayed.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Nasif_Azam · Accepted solution]

Hey @LD1 ,

To create a slicer for selecting a rate range in Power BI based on a measure, you can follow these steps:

1. Create a Measure

You need to create a DAX measure to represent the rate. For example, if you have a column named RateColumn in your dataset, you can create a measure like this:

RateMeasure = AVERAGE('Table'[RateColumn])

This measure will calculate the average rate for your data.

2. Create a Supporting Table for the Slicer

You will need a table to use in the slicer for selecting the range. This table will contain the possible range values, like this:

RateRange = GENERATESERIES(0, 100, 5)

This creates a table with values from 0 to 100, in steps of 5. You can adjust the range and step size to fit your needs.

3. Add a Slicer to the Report

Once you have the RateRange table, add it to the slicer visualization. You can add this table to your report and use it for the slicer selection.

4. Create a Filter Measure Based on Slicer Selection

Now you can create a measure that will filter the data based on the selected rate range. This measure will use the slicer value to filter your data. For example:

FilteredRate = 
CALCULATE(
    [RateMeasure],
    FILTER(
        'Table',
        'Table'[RateColumn] >= MIN('RateRange'[Value]) && 'Table'[RateColumn] <= MAX('RateRange'[Value])
    )
)

This DAX formula will filter your data based on the slicer selection and calculate the rate within the selected range.

5. Use the Measure in Visuals

Finally, use the FilteredRate measure in your visuals, and the slicer will control the range that is displayed.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[techies · Accepted solution]

Hi @LD1 i guess you can create a disconnected table like this for label slicer

RateRanges =

DATATABLE (

    "Label", STRING,

    "MinRate", DOUBLE,

    "MaxRate", DOUBLE,

    {

        { "0% - 10%", 0.00, 0.10 },

        { "10% - 20%", 0.10, 0.20 },

        { "20% - 30%", 0.20, 0.30 },

        { "30% - 40%", 0.30, 0.40 },

        { "40% - 50%", 0.40, 0.50 },

        { "50% - 60%", 0.50, 0.60 },

        { "60% - 70%", 0.60, 0.70 },

        { "70% - 80%", 0.70, 0.80 },

        { "80% - 90%", 0.80, 0.90 },

        { "90% - 100%", 0.90, 1.00 }

    }

)

And once your Rate measure is in place, then create this dax measure

IsInSelectedRateRange =

VAR SelectedMin = MIN(RateRanges[MinRate])

VAR SelectedMax = MAX(RateRanges[MaxRate])

VAR CurrentRate = [Your rate measure]

RETURN

    IF (

        CurrentRate >= SelectedMin && CurrentRate <= SelectedMax,

        1,

        0

    )

Then use the IsInSelectedRateRange measure in your final measure like this

Filtered Value =

IF (

    [IsInSelectedRateRange] = 1,

    SELECTEDVALUE(sales[Value]),

    BLANK()

)

[mark_endicott]

@LD1 - You can only do this in Power BI be creating a Numeric Range Parameter, but this will have to be done in the Semantic model which you mention is owned by IT. 

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-what-if

When you set this up, it will give you a column that can be used in a slicer, and a measure that will display the value, and can be referenced in other DAX measures. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[LD1]

Hi @mark_endicott , thank you for your answer. 

I tried something with parameters but the formula doesn't work. I don't know where I can put Min & Max and you? 

Here you can find my little example, 

Thank you a lot, 

LD

[mark_endicott · Accepted solution]

@LD1 - You can do this:

VAR SelectedMin = MIN(Parameter[Value]) * 100
VAR SelectedMax = MAX(Parameter[Value]) * 100
VAR CurrentRate = [Your % measure]
RETURN
    IF (
        CurrentRate >= SelectedMin && CurrentRate <= SelectedMax,
        1,
        0
    )

You can then put this measure on your visual to filter to 1 only:

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!