topic Using multiple IF statements to check value of field in DAX Commands and Tips

Using multiple IF statements to check value of field

Anonymous — Fri, 02 Sep 2022 13:59:33 GMT

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?

Re: Using multiple IF statements to check value of field

amitchandak — Fri, 02 Sep 2022 14:36:43 GMT

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

Re: Using multiple IF statements to check value of field

Anonymous — Fri, 02 Sep 2022 14:47:34 GMT

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.

Re: Using multiple IF statements to check value of field

Anonymous — Tue, 06 Sep 2022 15:39:14 GMT

That did the job! Thank you.