Two identical VARs within Measure - One doesn't work

DAX DAX Calculations
1

Hi

I have a problem where two (almost) identical VARs within a single measure where one works fine but the other does not.

I have attached the pbix file. The measure is B/S Template within Balance Sheet Measures.

The logic in lines 6 to 9 works very well.

However the same logic between lines 27 to 30 does not. This is generating figures that are just wrong.

The results can be seen in report on tab Page 2.

All help gratefully received.

Paul

Accounts.pbix (1.2 MB)

2

@PaulBoyes
Took a look at the file and that’s quite the function you got going on there :slight_smile: Any chance you can further explain what vLine1Value should be computing vs. what it is?

3

please forgive me if I’m overlooking something obvious here, but how can any value be ‘greater than’ three empty spaces?

looking at line 27 of the measure

VAR vLine1Value = IF(vCurrentType = “Calculated” && vop1 > " " && vOp1 <> “adr”

and yes, I realize that in this quote you don’t see all three blanks, but they do appear in the file

4

Hi Nick

Thank you for getting back to me. I am not entirely surprised there are questions.

What we are trying to achieve is to create a formatted balance sheet according to the users’ requirements for appearance and method of calculation. This is defined internally in our accounting software and exported to Power BI.

If you look at the table Template - Balance Sheet and make sure it is sorted ascending by sequence number that will give you the idea of what we are doing.

You will see that there are three line types, Header, Data and Calculated.

Data lines are easy as each has an associated account code and that is being achieved in the calculation in lines 6 to 9 in the measure. That simply adds up the records in NlTransactionsPosted with the same account code.

With the calculated line types we are interpreting the the instructions which refer to data lines by sequence number to find its account code and then add those transactions up.

The actual calculation commands are add, sub, div, %, cpy, adr. For the moment we are just working with the add command just to get the principle correct. The others, with the exception of adr (add up a range of lines) will be simple.

In the Measure i have used things like <> to mean not equal to either blank or adr but in fact it makes no difference because if they are removed it does not change things.

If you need anything more please let me know and thank you for taking the trouble.

Best regards

Paul

We are interested in the calculated lines

5

Hi
I have figured it out.

The relationship between Template - Balance Sheet is Nominal Code and not the Line1Nominal etc.

Will have to create a temporary relationship for those fields in Measure.

Thanks for looking at it

Best regards

Paul

6

@PaulBoyes
I spent some time and I thought it may be a relationship issue, but was having a hard time following.

One thing that I have found to be helpful, is to create actual separate measures (like your select values) and you can still use the variables you have, but if they are separate measures you can use them to troubleshoot and user else where w/o rewriting.

Also, I noticed that you are checking for " ", which can get hard to check for, if like @Heather mentioned, there are too many spaces or not enough. Recipe for an error that would be really hard to find.

I tried using ISBLANK, but that didnt work since in your data table they are not blank. They are empty, but not blank. So I did something to check for length. If the length was 0 (which would be empty ) then the selected value should be blank ( or whatever you would want) otherwise use the selected value

Selected Balance Sheet Op1 = 
Var _SelectedValue =
    SELECTEDVALUE('Template - Balance Sheet'[Op1])

Var _Length = 
    LEN( _SelectedValue)

RETURN
IF(_Length=0,BLANK(),_SelectedValue )

image
image783×578 39 KB

Just some ideas on what I have found helpful