topic Re: DAX POWER Function (aka '^' or 'to the power off') in DAX Commands and Tips

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

shstrr — Mon, 29 Apr 2024 20:42:33 GMT

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

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

lbendlin — Mon, 29 Apr 2024 21:54:41 GMT

Not seeing the issue here?

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

shstrr — Tue, 30 Apr 2024 01:23:38 GMT

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.

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

lbendlin — Tue, 30 Apr 2024 11:05:38 GMT

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

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

shstrr — Tue, 30 Apr 2024 15:26:37 GMT

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