DAX FORMULA VARIABLE HELP

[powerBIjyd]

Hi All,

I have a simple dax statement which keeps returning in error as seen below. I have tried debugging it several times and it is clear that the cause of the error is the DivBy variable used in the ANS variable, however I am still unable see why this simple statement is not executing successfully. Is anyone able to suggest a solution for this issue?

Thanks in advance

[powerBIjyd · Accepted solution]

Thanks guys for you help. I wondered what will happen if the formula returned negative values and the penny dropped. Here is the formula that worked.

[powerBIjyd]

I have also removed all columns from the table that are not relevant for this report

[PaulDBrown]

You don't need the function VALUE (which is used to convert a text string to a number). Try removing it, though I don't think it will be the culprit, but you never know...

[powerBIjyd]

Thanks for your response PaulDBrown,

This is actually for a measure. Indeed VALUE does not really serve a purpose. Using it does not actually affect the result.

[PaulDBrown]

it's hard to pinpoint the issue though you could use the performance analyzer or DAX studio to analyze the measure. 
BTW, the best way to do divisions is with the DIVIDE function. 
So, your measure should be:

'Percentage Margin = DIVIDE ([Buying FX Rate] , [Benchmark FX Rate]) - 1

the performance depends on what the two measures you are using in this division, however. 
Can you post the code for both these measures, please?

[powerBIjyd]

Thanks PaulDBrown,
This must be one for the ages. Thanks for you help and suggestions. I've actually tried several times to analyse this measure with DAX studio along with all the dependant measures. I tried all the different possible ways of expressing this formula and I get the same unhelpful error message. One thing I know for sure is if I change the operator to * or / the formula works without issue but when the operator is + or -  then it does not work. This formula also works perfectly in excel so I am still puzzled 

[daxer-almighty]

Some questions. 1) Why do you use VALUE? 2) How slow and resource intensive are the two measures used to caculate BenchFX? 3) What's the point of using CALCULATE in the variable? Are you creating a measure or a calculated column? 4) What's the use of CALCULATE in ANS? What's the point of VALUE in calculating the ANS variable?

There are many issues with this code. And I suspect there are even more issues with the other 2 measures... Also, using measures in calculated columns is not the best idea for many many reasons. If you try to use measures in calc column in a fact table - FORGET IT. This will never work.

[powerBIjyd]

Thanks for your responses daxer-almighty,

This is actually for a measure. However, the CALCULATE &  VALUE does not really affect the result. But I only included it while I was trying to figure out the issue the originally it was just a simple dax formula for a measure as follows:

'Percentage Margin = CALCULATE( ([Buying FX Rate] / [Benchmark FX Rate]) - 1)'

which was giving me the same error message. So I decided to use a proper dax statement with variables so I can track which of the variables was causing the problem. The problem is with the DivBy variable,  the formula used for the BenchFX variable works without any issue. I am puzzled as to why a simple variable like DivBy was returning an error message.