cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
milkywaypowerbi
Helper II
Helper II

Transferring DAX formulas from Powerbi to Power Pivot in Excel

Hi, 

I currently have this set of codes in Powerbi which is functional. However, I would like to transfer the DAX codes to Excel as a measure for Power Pivot. However, the syntax error kept appearing. Can someone pls assist me with the syntax issue on Excel.

 

NPV Test 001 =
VAR _selfamily =
if(HASONEVALUE( Family[Family]),VALUES(Family[Family]),0)
VAR _rate =
SWITCH ( _selfamily, "Book", 0.10, "Pen", 0.08 )
VAR _selyear =
if(HASONEVALUE(Year[Year]),VALUES(Year[Year]),0)
VAR _diffyear =
_selyear - SWITCH ( _selfamily, "Book", 2020, "Pen", 2019 )
RETURN
IF (
ISBLANK ( _diffyear )
|| _diffyear < 0,
BLANK (),
DIVIDE ( 1, POWER ( 1 + _rate, _diffyear ), 0 ) * Operating Profit)

2 REPLIES 2
m3tr01d
Continued Contributor
Continued Contributor

@milkywaypowerbi Hmm, I don't see anything that looks an unsupported DAX expressions in Power Pivot.

I might be wrong but I think Power Pivot needs to have ":=" instead of "=" when declaring a measure?

Hi @m3tr01d 

 

Thank you so much! That was the issue.

 

I am facing a new error as the operating profit has negative values for some year so there's an error showing : An arguement of function "POWER" has the wrong data type or the result is too large or small.

 

How can I modify this code for it to run for negative operating profit numbers.

 

Thank you!!

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors