DAX IF && || Statement for new Column in PowerBI Desktop

[mananthakore]

HEllo Guys,

I am preety new to this powerBI Desktop . I am trying to add new column with complex IF AND OR Statement.

I am getting 90% output from this formula, but one condition just does not seem like to working and i have no idea why?

here is the formula:

Budgeted Cost = IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "1" && vTransactionHistoryAlt_FMInc[Site]="3450" || vTransactionHistoryAlt_FMInc[Site]="3240" || vTransactionHistoryAlt_FMInc[Site]="3530" ,"1499",if(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)="1" && vTransactionHistoryAlt_FMInc[Site]="3250" || vTransactionHistoryAlt_FMInc[Site]="3580" || vTransactionHistoryAlt_FMInc[Site]="3500" || vTransactionHistoryAlt_FMInc[Site]="3310" ,"881", IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "1", "227", IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "2" && vTransactionHistoryAlt_FMInc[Site]="3450" || vTransactionHistoryAlt_FMInc[Site]="3240" || vTransactionHistoryAlt_FMInc[Site]="3530" ,"5377",IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "2" && vTransactionHistoryAlt_FMInc[Site]="3250" || vTransactionHistoryAlt_FMInc[Site]="3580" || vTransactionHistoryAlt_FMInc[Site]="3500" || vTransactionHistoryAlt_FMInc[Site]="3310" ,"3048", "262")))))

All my criteria is getting satisfied , except where in 5th IF statement, its keep coming with result based on 4th criteria, as if 5th IF statement does not exist.

Can you guys help it to figure it out what is wrong with 5th IF statement, its same as 1'st, 2nd and 3rd and 4th,  those are workign fine.

Thanks in advance

[v-yuezhe-msft · Accepted solution]

Hi @mananthakore,

I am not sure what is your sample data and what is your expected result.

Please use the following DAX to calculate Budgeted Cost and check if you get desired result

Budgeted Cost = IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "1" && (vTransactionHistoryAlt_FMInc[Site]="3450" || vTransactionHistoryAlt_FMInc[Site]="3240" || vTransactionHistoryAlt_FMInc[Site]="3530" ),"1499",

if(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)="1" && (vTransactionHistoryAlt_FMInc[Site]="3250" || vTransactionHistoryAlt_FMInc[Site]="3580" || vTransactionHistoryAlt_FMInc[Site]="3500" || vTransactionHistoryAlt_FMInc[Site]="3310") ,"881", 

IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "1", "227", IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "2" && (vTransactionHistoryAlt_FMInc[Site]="3450" || vTransactionHistoryAlt_FMInc[Site]="3240" || vTransactionHistoryAlt_FMInc[Site]="3530" ),"5377",

IF(right(vTransactionHistoryAlt_FMInc[Last 4 Digit],1)= "2" && (vTransactionHistoryAlt_FMInc[Site]="3250" || vTransactionHistoryAlt_FMInc[Site]="3580" || vTransactionHistoryAlt_FMInc[Site]="3500" || vTransactionHistoryAlt_FMInc[Site]="3310" ),"3048", "262")))))

 


Thanks,
Lydia

[himanshu56]

Hi @mananthakore

It is very difficult to read your formula with several if's.

I would advise you to use SWITCH dax instead of IF.

SWITCH(
    TRUE()
    ,<boolean expression>, <result if true>
    ,<boolean expression>, <result if true>
    , ..., ...
    ,<else condition - no trues above>
)

To know more about how to use SWITCH please have a look at the below link:

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true

Thanks,

Himanshu