Solved: DAX formula help for multiple IF statements

Kelsey024 · ‎01-12-2018

I am trying to create a calc column (“Meter Charges by Acct type and season”) to calculate out the metered charges based on consumption for a specific account depending on Account type AND season (summer or winter). I am able to work out the formula in DAX if I am passing only 1 condition statement, but I need to be able to look at my conditions and then either multiply by the winter rate or else by the summer rate for 7 different account types all in 1 column.

This is more or less what I want:

ACCT TYPE	Season	Consumption	Meter Charges by Acct type and season
DF	Winter	120	277.2
IRR-R	Winter	4	0.96
IRR	Winter	5	5.3
BLDG	Winter	34	78.54
POOL	Winter	18	19.08
FTN	Winter	57	60.42
FNT	Winter	2	2.12

This is what I was trying to pass:

Acct Type Calc Rates = IF('DETAIL WATER'[ACCT TYPE]="IRR" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*1.06,'DETAIL WATER'[CONS]*2.65)

OR

IF('DETAIL WATER'[ACCT TYPE]="POOL" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*1.06,'DETAIL WATER'[CONS]*2.65)

OR

IF('DETAIL WATER'[ACCT TYPE]="FTN" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*1.06,'DETAIL WATER'[CONS]*2.65)

OR

IF('DETAIL WATER'[ACCT TYPE]="FNT" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*1.06,'DETAIL WATER'[CONS]*2.65)

OR

IF('DETAIL WATER'[ACCT TYPE]="BLDG" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*2.31,'DETAIL WATER'[CONS]*2.31)

OR

IF('DETAIL WATER'[ACCT TYPE]="DF" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*2.31,'DETAIL WATER'[CONS]*2.31)

OR

IF('DETAIL WATER'[ACCT TYPE]="IRR-R" && ('DETAIL WATER'[Season]="Winter"),'DETAIL WATER'[CONS]*0.24,'DETAIL WATER'[CONS]*0.24)

I was able to get Power BI to accept the syntax if I used a logical like || between each statement, but then it only returned 1’s and 0’s and not the calculation.

What do I need to do in order to make this work? Is it possible?

Thank you!

-Kelsey

MarkS · ‎01-12-2018

Hi @Kelsey024

I think that this will do what you are looking for

Meter Charges by Acct type and Season = 
'DETAIL WATER'[Consumption]
    * IF (
        'DETAIL WATER'[Season] = "Winter",
        IF (
            'DETAIL WATER'[Acct Type] = "IRR"
                || 'DETAIL WATER'[Acct Type] = "POOL"
                || 'DETAIL WATER'[Acct Type] = "FTN"
                || 'DETAIL WATER'[Acct Type] = "FNT",
            1.06,
            IF (
                'DETAIL WATER'[Acct Type] = "BLDG"
                    || 'DETAIL WATER'[Acct Type] = "DF",
                2.31,
                .24
            )
        ),
        IF (
            'DETAIL WATER'[Acct Type] = "IRR"
                || 'DETAIL WATER'[Acct Type] = "POOL"
                || 'DETAIL WATER'[Acct Type] = "FTN"
                || 'DETAIL WATER'[Acct Type] = "FNT",
            2.65,
            IF (
                'DETAIL WATER'[Acct Type] = "BLDG"
                    || 'DETAIL WATER'[Acct Type] = "DF",
                2.31,
                .24
            )
        )
    )

However I would suggest creating a rates table and using the lookup function. Rate Table like the following related to the other table by ACCT Type

Then the calculated column formula

Rate = LOOKUPVALUE(Rates[Rate],Rates[Season],'DETAIL WATER'[Season],Rates[ACCT Type],'DETAIL WATER'[Acct Type])

would get the correct rates and is easier to maintain if the rates change.

View solution in original post

Phil_Seamark · ‎01-12-2018

HI @Kelsey024

Here is an alternative. Personally I'd put the weightings in a different table and join to that for the calcs

Acct Type Calc Rates = 

'DETAIL WATER'[CONS] * 
    SWITCH(
        TRUE() ,
        'DETAIL WATER'[ACCT TYPE]="IRR" && 'DETAIL WATER'[Season]="Winter" , 1.06 ,
        'DETAIL WATER'[ACCT TYPE]="IRR" , 2.65 ,
        ----------------------------------------------------------------------------
        'DETAIL WATER'[ACCT TYPE]="FTN" && 'DETAIL WATER'[Season]="Winter" , 1.06 ,
        'DETAIL WATER'[ACCT TYPE]="FTN" , 2.65 ,
        ----------------------------------------------------------------------------
        'DETAIL WATER'[ACCT TYPE]="FNT" && 'DETAIL WATER'[Season]="Winter" , 1.06 ,
        'DETAIL WATER'[ACCT TYPE]="FNT" , 2.65 ,
        ----------------------------------------------------------------------------
        'DETAIL WATER'[ACCT TYPE]="BLDG" && 'DETAIL WATER'[Season]="Winter" , 2.31 ,
        'DETAIL WATER'[ACCT TYPE]="BLDG" , 2.31 ,
        ----------------------------------------------------------------------------
        'DETAIL WATER'[ACCT TYPE]="DF" && 'DETAIL WATER'[Season]="Winter" , 2.31 ,
        'DETAIL WATER'[ACCT TYPE]="DF" , 2.31 ,        
        ----------------------------------------------------------------------------
        'DETAIL WATER'[ACCT TYPE]="IRR-R" && 'DETAIL WATER'[Season]="Winter" , 0.24 ,
        'DETAIL WATER'[ACCT TYPE]="IRR-R" , 0.24  ,
        1
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Tatyasaheb · ‎10-29-2018

Below if and code is not working in adding the column : need help

Break Excceded = IF(AND('GCC Break'[Unavailable Code]="Break",'GCC Break'[Unavailable Time]>30.5),1,IF(AND('GCC Break'[Unavailable Code]="Lunch",'GCC Break'[Unavailable Code]>30.5),1,IF(AND('GCC Break'[Unavailable Code]="Personal",'GCC Break'[Unavailable Time]>6.5),1,0)))

and below is the error:

DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

Phil_Seamark · ‎01-12-2018