Forum Discussion

Kelsey024's avatar
Kelsey024
Frequent Visitor
8 years ago
Solved

DAX formula help for multiple IF statements

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 (su...
Need Help
  • MarkS's avatar
    MarkS
    8 years ago

    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.

  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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
        )
Tatyasaheb's avatar
Tatyasaheb
New Member
7 years ago

 

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.