Multiple IF/AND for text fields

[srobs]

Hi, I'm pretty new to Power BI and not sure exactly what I can/can't do. 

I have quite a big data set of impoterd JIRA defect tickets, 2 of the columns are "Defect Occurance" and "Defect Severity" and each have values low, medium, high etc. I want to write a DAX conditional for acceptability that says - if occurance is high and severity is high then fill in 3rd column with unacceptable - and then replicate this for all the variables. 

I've tried a few variations but I'm struggling, I get an error even trying to call out the columns I want in the measure, do I need to transform this data first? It's an appended sheet called "all defects" so far I've tried; 

Defect Acceptability =
SWITCH (
TRUE (),
AND([Defect Severity]Low, [Defect Occurance]Low), "Acceptable",
AND([Defect Severity]Low, [Defect Occurance]Medium), "Acceptable",
AND([Defect Severity]Low, [Defect Occurance]High), "Acceptable",
AND([Defect Severity]Low, [Defect Occurance]Very High), "Justify",

)

But I just get loads of syntax errors. 

Any ideas? Thanks. 

[srobs · Accepted solution]

Turned I had a couple of issues! 

1. I needed to select "new column" not "new measure" in order to pull from my data

2. I had left a comma at the end

3. I spelt severity wrong and had copy pasted it (duh)

In the end it looked like this, but with all 16 variables, and it worked 🙂

Column =

SWITCH(

    true(),

    [Defect Severity]="low"&&[Defect Occurrence]="low","Acceptable",

    [Defect Severity]="Low"&&[Defect Occurrence]="Medium","Acceptable"

)

[v-rzhou-msft]

Hi @srobs ,

Could you tell me if your issue has been resolved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your issue or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou

[123abc]

It looks like you are on the right track with using the SWITCH function, but there are some syntax errors in your DAX code. Also, it seems like there might be a mix-up in the order of your conditions. Here's a corrected version of your DAX formula:

Defect Acceptability =
SWITCH (
TRUE (),
AND([Defect Severity] = "Low", [Defect Occurrence] = "Low"), "Acceptable",
AND([Defect Severity] = "Low", [Defect Occurrence] = "Medium"), "Acceptable",
AND([Defect Severity] = "Low", [Defect Occurrence] = "High"), "Acceptable",
AND([Defect Severity] = "Low", [Defect Occurrence] = "Very High"), "Justify",
// Add similar conditions for other severity levels
AND([Defect Severity] = "Medium", [Defect Occurrence] = "Low"), "Your_Result_For_Medium_Low",
AND([Defect Severity] = "Medium", [Defect Occurrence] = "Medium"), "Your_Result_For_Medium_Medium",
AND([Defect Severity] = "Medium", [Defect Occurrence] = "High"), "Your_Result_For_Medium_High",
AND([Defect Severity] = "Medium", [Defect Occurrence] = "Very High"), "Your_Result_For_Medium_Very_High",
// Repeat for other severity levels
// Add a default case if none of the conditions match
"Other_Result"
)

Make sure to replace "Your_Result_For_X_Y" with the actual result you want for each combination of severity and occurrence. Also, include conditions for other severity levels as needed.

Note: I assumed that your severity and occurrence values are text strings. If they are numerical, you should remove the quotation marks around the values in the conditions.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.