cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
acalderon Frequent Visitor
Frequent Visitor

DAX IF statement help

I have a basic task that will take our budget status, scope status and schedule status and return an "overall status" based on the color of each of the 3 statuses mentioned above

The 3 statuses can either be Green, yellow, or red

 

If all 3 statuses are green, then overall status =  Green

If 1 status is yellow, then overall status = Yellow

If 1 status is red, then overall status = Red

 

^^ I can't quite figure out how to create this using dax, any help will be greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
hthota Member
Member

Re: DAX IF statement help

Hi @acalderon

 

I hope this would help you.

 

Dax Function: 

Overall Status = IF(OR(OR(Sheet1[Budget status]="Red",Sheet1[Schedule status]="Red"),Sheet1[Scope status]="Red"),"Red",IF(OR(OR(Sheet1[Budget status]="Yellow",Sheet1[Schedule status]="Yellow"),Sheet1[Scope status]="Yellow"),"Yellow","Green"))

 

 

acalberon.PNG

Report Link for your reference: https://app.powerbi.com/groups/me/reports/b1a5f4ce-0642-4844-b828-b2563e768479?ctid=470e1ac4-5b55-48...

 

6 REPLIES 6
Super User
Super User

Re: DAX IF statement help

Probably best to create a measure that looks at each of the columns and checks if any of them are red, and also create a measure that looks at all of the columns and sees if any of them are yellow. You can then do something like:

 

OverallStatus = if([redcheck]=TRUE(), "Red",if([yellowcheck]=TRUE(), "Yellow", "Green"))

Super User
Super User

Re: DAX IF statement help

Just solved something similar, see if this helps:

 

https://community.powerbi.com/t5/Desktop/Switch-amp-Calculate-Issue/m-p/352890#M158765

Performance =
VAR Score = (RELATED('Rating-FY17'[FY17 Rating])+RELATED('Rating-FY16'[FY16 Rating]))/2
VAR Rating = SWITCH(
TRUE(),
Score = 5, "Top Performer",
Score >= 4 && Score < 5,"High Performer",
Score >= 3 && Score < 4,"Base Performer",
Score > 1 && Score < 3,"Low Performer",
"N/A"
)
RETURN IF(ISBLANK(RELATED('Rating-FY17'[FY17 Rating])) || ISBLANK(RELATED('Rating-FY16'[FY16 Rating])),"NA

So, in your case it might be something like:

 

Overall Status =
VAR Status1 = Table1[Status]
VAR Status2 = Table2[Status]
VAR Status3 = Table3[Status]

VAR OverallStatus = SWITCH(
TRUE(),
Status1 = "Green" && Status2 = "Green" && Status3="Green", "Green",
Status1
)
RETURN OverallStatus

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


hthota Member
Member

Re: DAX IF statement help

Hi @acalderon

 

I hope this would help you.

 

Dax Function: 

Overall Status = IF(OR(OR(Sheet1[Budget status]="Red",Sheet1[Schedule status]="Red"),Sheet1[Scope status]="Red"),"Red",IF(OR(OR(Sheet1[Budget status]="Yellow",Sheet1[Schedule status]="Yellow"),Sheet1[Scope status]="Yellow"),"Yellow","Green"))

 

 

acalberon.PNG

Report Link for your reference: https://app.powerbi.com/groups/me/reports/b1a5f4ce-0642-4844-b828-b2563e768479?ctid=470e1ac4-5b55-48...

 

acalderon Frequent Visitor
Frequent Visitor

Re: DAX IF statement help

@Greg_Deckler

OverallStatus = Var Status1 = Projects[BudgetStatus]
Var Status2 = Projects[ScopeStatus]
Var Status3 = Projects[ScheduleStatus]

Var OverallStatus = SWITCH(TRUE(),
Status1 = "Green" && Status2 = "Green" && Status3="Green", "Green", Status1)

Return OverallStatus

 

I tested the above code out with a project that had schedule status as yellow, but it still returned overall status as "Green"
my understanding from the above is that variables status1, status2 and status3 hold a value for whatever color status it is

the bolded part is where i'm confused, is there something i'm missing to tell it to return red or yellow if either of the variables is red or yellow?

hthota Member
Member

Re: DAX IF statement help

Use the Dax function with OR function as given above.

I hope i will work.

acalderon Frequent Visitor
Frequent Visitor

Re: DAX IF statement help

@hthota that worked, verified with different projects. THank you!