Forum Discussion
IF with multiple conditions
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!
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.
3 Replies
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.
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