Forum Discussion
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 (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
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.
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 )
4 Replies
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.
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!
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 )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.
