Using multiple IF statements to check value of field

[Anonymous]

I am trying to use conditional formatting on a column based on the value of a field.  In this case, this is for a risk register.  If the relative risk ranking is Critical, set the measure to 1 so I can use that in a rule for formatting.

Measure Risk Ranking Critical =

SWITCH(

    TRUE(),

    'IT Risk Register'[Relative Risk Ranking] = "Critical", 1,

    'IT Risk Register'[Relative Risk Ranking] = "High", 2,

    'IT Risk Register'[Relative Risk Ranking] = "Moderate", 3,

    'IT Risk Register'[Relative Risk Ranking] = "Low", 4

)

Based on the value returned I'll use the conditional formatting using rules

I'm getting the error, "A single value for column 'Relative Risk Ranking' in table 'IT Risk Register' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

The column is a string that I'm trying to compare.  Is there a better/correct way to do this?

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try this measure or you can create a calculated column with your written measure

Measure Risk Ranking Critical =
SWITCH(
TRUE(),

MAX('IT Risk Register'[Relative Risk Ranking]) = "Critical", 1,
MAX('IT Risk Register'[Relative Risk Ranking]) = "High", 2,
MAX('IT Risk Register'[Relative Risk Ranking]) = "Moderate", 3,
MAX('IT Risk Register'[Relative Risk Ranking]) = "Low", 4
)

Regards,

Ashfiya

--------------------------------------------------------------------------------------------------------------------------

Did I help you today? Please mark my post as a solution and hit the Kudos button.

[Anonymous]

That did the job!  Thank you.

[amitchandak]

@Anonymous ,

Measure Risk Ranking Critical =
SWITCH(
TRUE(),

max('IT Risk Register'[Relative Risk Ranking] ) = "Critical", 1,
max('IT Risk Register'[Relative Risk Ranking] ) = "High", 2,
max('IT Risk Register'[Relative Risk Ranking] ) = "Moderate", 3,
max('IT Risk Register'[Relative Risk Ranking] ) = "Low", 4
)

You can also return color and use that using the field value option

How to do conditional formatting by measure and apply it on pie?
https://www.youtube.com/watch?v=RqBb5eBf_I4&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8L
https://community.powerbi.com/t5/Community-Blog/Power-BI-Conditional-formatting-the-Pie-Visual/ba-p/1682539
https://amitchandak.medium.com/power-bi-where-is-the-conditional-formatting-option-in-new-format-pane-66e0afcb15f3