DAX formula help for multiple IF statements

[Kelsey024]

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 · Accepted solution]

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.

[Tatyasaheb]

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 · Accepted solution]

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
        )

[MarkS · Accepted solution]

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.

[Kelsey024]

Both of these worked really well! I think you are right, it will be much easier for any future metered changes to use the lookupvalue function with a seperate table.

Thank you so much for your help!