What if parameter to define High risk, Medium risk and Low risk value

[BHAVI2803]

Hi Power BI Community,

I am facing issue while using What if Parameter to allow user to decide value for High risk, Medium risk and Low risk. Below steps I have used to implement the use case.

1. Created 2 what if parameter for High Risk and Medium risk, which allow user to set the upper limit for both of the categories with the ranges.

2. Created new measure (since calculated column would not be dynamic) using below DAX expression

Risk Category = if(AVERAGE([Column])<'High Risk Parameter'[High Risk Parameter Value],"High Risk",

                    if(AVERAGE([Column])>'Medium Risk Parameter'[Medium Risk Parameter Value],"Low Risk","Medium Risk"))

3. the above calculated working fine on conditional formatting of the report cell color, and even on table report and matrix report.

Issue:

 I have to create a bar graph where the created column should be used as dimension and will calculate product count in each risk category, like below:

 

I tried but it gives me blank values, since measure can not be used as dimension I think. Need help to find a work around.

If some one have worked similar kind of use case please share the solution.

Appriciate your time and support in this scenario. 

Thank you!

Bhawna Gupta

[mickey64 · Accepted solution]

For your reference.

Step 0: I use these data tables below.

'DATA' Table

'High Risk Parameter' Table

'Medium Risk Parameter' Table

Step 1: I make 2 slicers and make 5 measures.

M_HighRiskValue = SELECTEDVALUE('High Risk Parameter'[High Risk Parameter Value])

M_MediumRiskValue = SELECTEDVALUE('Medium Risk Parameter'[Medium Risk Parameter Value])

M_High Risk = COUNTX(FILTER('DATA','DATA'[Column]<[M_HighRiskValue]),'DATA'[ID])

M_Low Risk = COUNTX(FILTER('DATA','DATA'[Column]<[M_MediumRiskValue]),'DATA'[ID])

M_Medium Risk = COUNTROWS('DATA')-[M_High Risk]-[M_Low Risk]

Step 2: I make a 'Clustered column chart'.

[BHAVI2803]

Thank you so much for your solution, I have one more issue, is there any way to create filter for this risk category using what if parameter? Risk category: High Risk, Medium Risk and Low risk, which will slice the information on the desktop

Appriciate your response and time

[mickey64 · Accepted solution]

For your reference.

Step 1: I make a "Parameter". (The slicer is made automatically.)

Step 2: I make a graph.

[BHAVI2803]

This is perfect, can you please provide the sample data for the use of parameter if possible.

[mickey64]

For your reference. (^^)/

[BHAVI2803]

Perfect Thank you so much

[mickey64 · Accepted solution]

For your reference.

Step 0: I use these data tables below.

'DATA' Table

'High Risk Parameter' Table

'Medium Risk Parameter' Table

Step 1: I make 2 slicers and make 5 measures.

M_HighRiskValue = SELECTEDVALUE('High Risk Parameter'[High Risk Parameter Value])

M_MediumRiskValue = SELECTEDVALUE('Medium Risk Parameter'[Medium Risk Parameter Value])

M_High Risk = COUNTX(FILTER('DATA','DATA'[Column]<[M_HighRiskValue]),'DATA'[ID])

M_Low Risk = COUNTX(FILTER('DATA','DATA'[Column]<[M_MediumRiskValue]),'DATA'[ID])

M_Medium Risk = COUNTROWS('DATA')-[M_High Risk]-[M_Low Risk]

Step 2: I make a 'Clustered column chart'.