Using slicer for a measure

[Michael_Gold]

Hello,

I've created a measure called risk_flag that can receive the following values: No risk, low, medium, high

Risk_Flag = IF([Gap]>30,"High",IF([Gap]>15,"Medium",IF([Gap]>0,"Low","No risk")))

I set it to be a measure and not a column because the Gap is dynamic (user can change the required ship date by the slicer on the right and the gap will be updated accordingly), meaning that the risk_flag is also dynamic

As it's a measure, I can't add it to a slicer. How can I create a slicer to filter the matrix by the risk_flag measure values?

See link to G.drive with the file: Download file 

[Tutu_in_YYC · Accepted solution]

Hey Michael,

Look at this downloadable tutorial pbix. It involves creating a table like @edhans  mentioned.

[Tutu_in_YYC · Accepted solution]

Hey Michael,

Look at this downloadable tutorial pbix. It involves creating a table like @edhans  mentioned.

[edhans]

You cannot. A measure always returns a scalar value , or a single value. You are wanting it to return your 3-4 values that are possible and use it as a slicer. 

You will need to create a table (use Enter Data for an easy way) then use that table as your slicer. This is also a time when it is appropriate to use a Calculated Column, or a Custom Column in Power Query. The values of the column can be used as the slicer.

[Michael_Gold]

Hi,

Hi,

I am actually quite new to power bi. How can I use a table as a slicer?

I actually tried creating calculated columns but because I am using measures I can't connect between the two. My report is dynamic and is being influenced by a parameter value. That's why I did not user a calculated column

[edhans]

You create a table, then use that as a way to filter other data. Use the Enter Data feature.

I'd need to see more of what you are doing, but you could look at this article. It creates a table using the table constructor - see the Power BI dynamic Measure based on slicer selection section.
How to create a Measure based on Slicer in Power BI - EnjoySharePoint

I am not sure what you mean you cannot connect measures and calculated columns. They don't connect. It is either/or, or and if the Calc Column is your slicer value and the Measure is your calculation to show in the report. Calculated columns will not update based on parameters. 

So the dynamic measure above is the likely route. But again, we'd need to see more info.

[Michael_Gold]

Hi again,

You can download and look at the file:

https://drive.google.com/file/d/1kK1i3Iqh1B8saHPi7lH3FULrSb4TYRMz/view?usp=sharing

Hopefully this will give the understanding of what I'm trying to do

[edhans · Accepted solution]

Here you go @Michael_Gold 


I used this measure. You can see I kept your basic measure but assigned to to the varRiskAssessment variable, then used it in the Result variable.

Risk_Flag =
VAR varSelectedRisk =
    SELECTEDVALUE( 'Risk Slicer'[Risk] )
VAR varRiskAssessment =
    IF(
        [Gap] > 30,
        "High",
        IF(
            [Gap] > 15,
            "Medium",
            IF(
                [Gap] > 0,
                "Low",
                "No risk"
            )
        )
    )
VAR Result =
    SWITCH(
        TRUE,
        varSelectedRisk = "High"
            && varRiskAssessment = "High", "High",
        varSelectedRisk = "Medium"
            && varRiskAssessment = "Medium", "Medium",
        varSelectedRisk = "Low"
            && varRiskAssessment = "Low", "Low",
        varSelectedRisk = "No Risk"
            && varRiskAssessment = "No Risk", "No Risk",
        ISBLANK(varSelectedRisk), varRiskAssessment
    )
RETURN
    Result

then I set the measure filter on the table visual to hide when Risk_Flag was blank. If you don't do that, then the High/Med/Low will vanish from the Risk Flag, but the record will still show up as blank (though your conditional formats would still be there). I figured hiding the unselected value is what you wanted, thus the measure filter shown below.

The slicer came from a manually entered table. Go to Transform Data and you will see it, and click the Gear icon next to the Source step to edit that table.
See this PBIX.

It is a multi-step process, but hopefully you can follow my logic and example. Let me know if you have questions.

[Tutu_in_YYC]

This is a cool work around!

[Michael_Gold]

Hi edhans,

That's exactly what I was looking for, appreciate the suport!

[edhans]

Great @Michael_Gold - glad I was able to help.