DAX - Assign a category based on other column

[AmudhaKumaran]

Hi Team,

I'm trying to solve a simple DAX, but struck somewhere. I have a table like sample shared below. IDs are scored on different categories and their grade is marked by Category Zone (If Category Score between 3 & 5 then Green else if Category Score between 2 & 2.9 then Yellow else if Category Score between 1 & 1.9 then Red else if Category Score = 0 then Automatic Red else NA). And Current Overall Zone is based on Total Score with the same logic as in Category Zone.

Now, I want to change the Overall Zone for an ID to Red regardless of Total, if any of the Category Zone is Automatic Red.

Any suggestion would really help.

[amitchandak · Accepted solution]

@AmudhaKumaran , a new column

var _1 = countx(filter(Table, [ID] = earlier([ID]) && [Category Score] =0), [Category Score])
return
if(isblank(_1), [Current Overall Zone], "Red")

[AmudhaKumaran]

Hi @amitchandak ,

Thanks for your quick response. My issue is resolved now.