How to use a measure as Slicer?

This article will show how to add measure in the slicer. Now the first question is why do we need to add a measure in the slicer?

I got a situation where I have to roll up the granularity level and find the difference%, Difference bps, and bps range. To achieve it there are two ways.

1. Create a separate table at higher granularity by using group by in the power query or create the table using DAX using Summarize function.
2. To use the same table and create measures for difference%, Difference bps, and bps range then rollup will happen correctly.

I had the requirement of adding bps range in the slicer. So either way is to use the bps range column from the new table created for rolled-up granularity or to create a measure and use it in the slicer.

So, in this article, we will see all the steps to be followed to use a slicer as a column. I am using the default dataset provided in the Power BI Desktop.

Video – How to use measure in Slicer?

1. Create a Measure

First I have created all the required measures. I am creating a range of Sale Price column for demo purposes and then this Sale Price Range we will use in the slicer to filter the table.

Sale Price Range =
SWITCH (
    TRUE (),
    SUM ( financials[Sale Price] ) <= 100, “<=100”,
    SUM ( financials[Sale Price] ) > 100
        && SUM ( financials[Sale Price] ) <= 500, “>100 & <=500”,
    SUM ( financials[Sale Price] ) > 500, “>500”
)

Using the above DAX query, I have created a Sale Price Range Measure.

If you are struggling to learn DAX, then you can check Best Resources to learn DAX article which has list of resource which can really help you understand better.

If you are new to Power BI then you should check Ultimate Guide of Microsoft PowerBI Tutorials : Step by Step Approach article which has some of the best resources to learn Power BI free.

2. Create a Calculated Table

As we cannot directly use a measure in the slicer, we need to create a calculated table up to a granular level so that all the values of the measure are displayed. What I mean is, that if I remove the Date column from the table then the value of the Sale Price Range will be greater than 500 and we will get only >500 value in the Sale Price Range column.

Range Table =
CALCULATETABLE (
    SUMMARIZE (
        financials,
        financials[Date],
        financials[Country],
        financials[Product],
        “Range”, financials[Sale Price Range] (Measure)
    )
)

3. Create Filter Measure

Using the Range Table, the Sale Price Range column will be used in the slicer but how value selected in the slicer will filter the original table. For that, you will need a create a Range Filter using the below DAX Code.

Range Filter =
IF (
    ISBLANK ( SELECTEDVALUE ( ‘Range Table'[Range] ) ),
    1,
    IF (
        financials[Sale Price Range] = SELECTEDVALUE ( ‘Range Table'[Range] ),
        1,
        0
    )
)

This measure will be required to place in the visual filter pane of the table which will be created using a column from the original table.

Conclusion

That`s it, Now you will be able to filter the original table using the measure placed in the filter. I know you would be asking why to do all this instead create a calculated column but there is a problem will calculated column there is an aggregation if we try to roll up the levels while the measure provides flexibility so this use case can be useful while creating a self-service layer for the client.

However, you can decide whether you can use a calculated column or measure depending upon the situation.