Forum Discussion

Michael_Gold's avatar
Michael_Gold
Frequent Visitor
4 years ago
Solved

Using slicer for a measure

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"))...
Need Help
  • edhans's avatar
    edhans
    4 years ago

    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.

Michael_Gold's avatar
Michael_Gold
Frequent Visitor
4 years ago

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's avatar
edhans
Community Champion
4 years ago

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.

 

    • edhans's avatar
      edhans
      Community Champion
      4 years ago

      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.

      • Michael_Gold's avatar
        Michael_Gold
        Frequent Visitor
        4 years ago

        Hi edhans,

         

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