Blank Function Problem

[Lenni96]

Hey Community. I have a big problem by using comparisons with the BLANK Function.

My formula is:

CAGR = IF('010101'[2013]<0;50000;(IF(AND(IF('010101'[2013]=BLANK();'010101'[2012];'010101'[2013])>0;IF('010101'[2011]=BLANK();'010101'[2010];'010101'[2011])<0);ABS(((IF('010101'[2013]=BLANK();'010101'[2012];'010101'[2013])/(IF('010101'[2011]=BLANK();'010101'[2010];'010101'[2011])))^(1/3)-1)*100);(((IF('010101'[2013]=BLANK();'010101'[2012];'010101'[2013])/(IF('010101'[2011]=BLANK();'010101'[2010];'010101'[2011])))^(1/3))-1)*100)))

In the end, Power BI tells me that "an argument of function "power" has the wrong data type or the result is too large or too small."

I have no clue what I did wrong. It seems to be the Blank function because the same formula works in excel.

Thank you in advance!

Lenni96

[v-yulgu-msft]

Hi @Lenni96,

I have tested the formula using sample data created by myself, it worked fine, without prompting any error message. So, I assume it is related to your actual data. Please check the data type of each column to make sure it wasn't changed after import into Power BI desktop. If possible, please share your sample data so that we reproduce your issue and try to resolve it.

Best regards,
Yuliana Gu

[Lenni96]

My mistake was that I used too many parenthesis. The error report put me on the wrong track.

However, I still have to deal with the following problem:

- When I type in this part of my formula, I still receive the error report: "An argument of function 'POWER' has the wrong data type or the result is too large or too small."

((IF('080101'[2014]=BLANK();'080101'[2013];'080101'[2014])/IF(IF('080101'[2014]=BLANK();'080101'[2013];'080101'[2014])='080101'[2013];'080101'[2011];'080101'[2012]))^(1/3)-1)*100

(IF('080101'[2014]=BLANK();'080101'[2013];'080101'[2014])/IF(IF('080101'[2014]=BLANK();'080101'[2013];'080101'[2014])='080101'[2013];'080101'[2011];'080101'[2012])^(1/3)-1)*100

- However, if delete the underlined parenthesis, the formula works and I do not get any error reports. Nevertheless, the formula changes. What can i do to remain the results of the first formula withouth the error report?

[Lenni96]

Anyone?

[dedelman_clng]

Have you tried using the DIVIDE(A, B) function instead of the traditional A / B ?

[Lenni96]

Even If I try to avoid the BLANK Function and use "010101'[2013] =0" instead, it still shows me "An argument of function "power" has the wrong data type or the result is too large or too small.

[dedelman_clng]

"Power" is going to be the carat ^ symbol.  Check your order of operations and groupings (might need more parentheses) and make sure all the columns referenced are a number datatype.

Also, you can use this link to format the DAX code to be easier to read: http://www.daxformatter.com/

[Lenni96]

All the columns referenced are a number datatype. In respect of the order of operations and groupings, I tried some options but nothing really worked out.

[dedelman_clng]

Here is your formula formatted for DAX

CAGR = 
IF (
    '010101'[2013] < 0,
    50000,
    (
        IF (
            AND (
                IF ( '010101'[2013] = BLANK (), '010101'[2012], '010101'[2013] )
                    > 0,
                IF ( '010101'[2011] = BLANK (), '010101'[2010], '010101'[2011] )
                    < 0
            ),
            ABS (
                (
                    (
                        IF ( '010101'[2013] = BLANK (), '010101'[2012], '010101'[2013] )
                            / ( IF ( '010101'[2011] = BLANK (), '010101'[2010], '010101'[2011] ) )
                    )
                        ^ ( 1 / 3 )
                        - 1
                )
                    * 100
            ),
            (
                (
                    (
                        IF ( '010101'[2013] = BLANK (), '010101'[2012], '010101'[2013] )
                            / ( IF ( '010101'[2011] = BLANK (), '010101'[2010], '010101'[2011] ) )
                    )
                        ^ ( 1 / 3 )
                )
                    - 1
            )
                * 100
        )
    )
)

Since I don't have your data model I can't test the performance, but maybe looking at it spread out like this will help you think of a way forward. If you want to share an anonymized pbix file, I can play with that too.