IF with multiple conditions

[Anonymous]

Hi, I have this tree of conditions: 

and I traduce it to Power BI using the fields:

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="CLS" && TotalData[Total]<100 && TotalData[Status]="NO" ,"Green",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="CLS" && TotalData[Total]>100 && TotalData[Status]="NO" ,"Green",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="CLS" && TotalData[Total]<100 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="CLS" && TotalData[Total]>100 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="REL" && TotalData[Total]<100 && TotalData[Status]="NO" ,"Yellow",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="REL" && TotalData[Total]>100 && TotalData[Status]="NO" ,"Red",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="REL" && TotalData[Total]<100 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY19" && TotalData[WBS Status]="REL" && TotalData[Total]>100 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="CLS" && TotalData[Total]<10 && TotalData[Status]="NO" ,"Green",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="CLS" && TotalData[Total]>10 && TotalData[Status]="NO" ,"Red",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="CLS" && TotalData[Total]<10 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="CLS" && TotalData[Total]>10 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="REL" && TotalData[Total]<10 && TotalData[Status]="NO" ,"Yellow",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="REL" && TotalData[Total]>10 && TotalData[Status]="NO" ,"Red",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="REL" && TotalData[Total]<10 && TotalData[Status]="YES" ,"Green",

TotalData[Fiscal Year]="FY20" && TotalData[WBS Status]="REL" && TotalData[Total]>10 && TotalData[Status]="YES" ,"Green",

Which is the best practice to make the IF condition and generate a calculated column?

Thanks!

[v-yiruan-msft]

Hi @Anonymous ,

Please try this:

Scorecard Status =
IF (
    OR ( 'TotalData'[Fiscal Year] = "FY19", 'TotalData'[Fiscal Year] = "FY20" )
        && 'TotalData'[WBS Status] = "REL"
        && 'TotalData'[Total] < 100
        && 'TotalData'[Status] = "NO",
    "Red",
    (
        IF (
            OR ( 'TotalData'[Fiscal Year] = "FY19", 'TotalData'[Fiscal Year] = "FY20" )
                && OR ( 'TotalData'[WBS Status] = "CLS", 'TotalData'[WBS Status] = "REL" )
                && 'TotalData'[Total] > 100
                && 'TotalData'[Status] = "NO",
            "Yellow",
            "Green"
        )
    )
)

Best Regards

Rena

[Tad17 · Accepted solution]

Hey @Anonymous 

You can use the AND and OR functions or even embed IF statements in Power BI just like you can in excel if you have an if function with multiple criteria.

AND: https://docs.microsoft.com/en-us/dax/and-function-dax

OR: https://docs.microsoft.com/en-us/dax/or-function-dax

Depending on your situation you may also want to consider the SWITCH function: https://docs.microsoft.com/en-us/dax/switch-function-dax

Examples: https://community.powerbi.com/t5/Desktop/DAX-Measure-with-Nested-IF-Statements/td-p/113358

https://stackoverflow.com/questions/40254578/multiple-if-statements-in-dax

If this helps please kudo.

If this solves your problem please accept it as a solution.

[Anonymous]

Thanks! with SWITCH function is working, I just validate it.

🙂