DAX POWER Function (aka '^' or 'to the power off')

[shstrr]

I am getting an Error when I am trying to use the standard POWER function cacluation in Power Pivot (DAX) environment. 

Specifically I want to create a Calculated Column 'Impact' based on 'simple' Arithmetic calculation of two columns

'Price Index' ^ 'Coefficient'.  

'Coefficient' is a fixed value that is unique to each Product. 'Price Index' is a Calculated Column where each Product's price in 2024 is indexed to the Product's price in 2023. ie for Product A June '24 'Price Index' = 'June '24 Price'/'June '23 Price'

Intuitively I want every row where a 'Price Index' exists to have an 'Impact' value ie

Product A - June '24  1.1^0.31 = 1.030

Product A - July '24 1.105^.31 = 1.032

Product B - June '24 1.140 ^ .20 = 1.027

repeat 

I am writing the formula in Dax as [Price Index]^[Coefficient] which results in an 'Error'. I am ok with an Error or missing values in rows where 'Price Index' does not exit. I have double checked using ISNUMBER function that both 'Price Index' and 'Coefficinent' are formatted as a decimal number. I have tried re-writing the formula as POWER ([Price Index], [Coefficient]) with similar results. 

I am struggling to find documentation on the 'POWER' function properties to help me resolve this error. Any tips? Thank you

Product	Period	Price	Price Index	Coefficient	Impact
Product A	June '23	100		0.31
Product A	July '23	95		0.31
Product A	August '23	90		0.31
Product A	June '24	110	1.100	0.31	1.030
Product A	July '24	105	1.105	0.31	1.032
Product A	August '24	99	1.100	0.31	1.030
Product B	June '23	50		0.2
Product B	July '23	52		0.2
Product B	August '23	53		0.2
Product B	June '24	57	1.140	0.2	1.027
Product B	July '24	56	1.077	0.2	1.015
Product B	August '24	52	0.981	0.2	0.996

[lbendlin · Accepted solution]

you can replace COALESCE with IF(ISBLANK([Price Index]),1,[Price Index])

[lbendlin]

Not seeing the issue here?

[shstrr]

Thank you so much for your quick reply. I am not seeing the COALESCE function in Power Pivot. Is your example executed in Power Pivot or another tool. Sorry for the beginner questions. I am just getting started in this field. 

[lbendlin · Accepted solution]

you can replace COALESCE with IF(ISBLANK([Price Index]),1,[Price Index])

[shstrr · Accepted solution]

thank you so much for your reply! This did not solve the issue, but your comment prompted me to review the original problem and indentify the source of the error. I shared an incomplete Table. True data looked like this, with the ∞ value in rows where 'Price Year Ago' was 0. It seems like DAX could not compute the POWER(∞, [Coefficient]) for certain rows and stopped calculating for all other rows as well.

I re-wrote the 'Price Index' calculated column with a IFERROR([Price]/[Price YA],1) so now every row is populated with either a 1 or 'Calculated Price Index'. 

Thank you again for your help. 

Product	Period	Price	Price Index	Coefficient	Impact
Product A	June '23	100	∞	0.31
Product A	July '23	95	∞	0.31
Product A	August '23	90	∞	0.31
Product A	June '24	110	1.1	0.31	1.03
Product A	July '24	105	1.105	0.31	1.032
Product A	August '24	99	1.1	0.31	1.03
Product B	June '23	50	∞	0.2
Product B	July '23	52	∞	0.2
Product B	August '23	53	∞	0.2
Product B	June '24	57	1.14	0.2	1.027
Product B	July '24	56	1.077	0.2	1.015
Product B	August '24	52	0.981	0.2	0.996