Forum Discussion

Anonymous

Not applicable

1 year ago

Solved

Create a slicer with a Mesure

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,

rate

segment

Slicer

techies
1 year ago
Hi Anonymous 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
1 year ago
Hey Anonymous ,
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
mark_endicott
1 year ago
Anonymous - 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!

5 Replies

mark_endicott
Super User
1 year ago
Anonymous - 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!
- Anonymous
  Not applicable
  1 year ago
  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
    Super User
    1 year ago
    Anonymous - 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!
techies
Super User
1 year ago
Hi Anonymous 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
Super User
1 year ago
Hey Anonymous ,
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

Forum Discussion

Create a slicer with a Mesure

5 Replies

Related content

DAX Mesure

Creating date slicer removes data

Creating a dynamic slicer

Improve my dax mesure

Slicer based on Mesure