## Circular Reference

Anything related to PowerPivot and DAX Formuale
mmaher
Posts: 89
Joined: Mon Sep 08, 2014 12:00 pm

### Circular Reference

I have a lot of golf scores.

A sample of my data looks like this: (sample spreadsheet attached):

2017-11-21_11-43-16.png (22.6 KiB) Viewed 1307 times

The objective is to calculate the column Hard Cap Index using DAX. I can obviously do it in Excel, as per the spreadsheet.

The first column is the Competition Date, and the second, the Anchor Date, is one year prior.

The column E20 is a moving average of the best Eight of the last 20 scores. I can get this in DAX thanks to Avi in a previous post.

The Anchor is the lowest earlier value of the Hard Cap Index since the Anchor Date. For Round 1 there is no earlier round, so there is no Anchor. But for all succeeding rounds, there is value.

The column E20 - Anchor is as it says, and has a value for all rounds except the first.

Normally, the Soft Cap Index and the Hard Cap Index is the same as the E20.

The Soft Cap is different to the E20, ie the Index is capped, when the E20 - Anchor is greater than 3.0.

It is calculated as the Anchor + 3 + 0.5 * ([E20 - Anchor] - 3). So, in other words, once the E20 rises above the anchor by 3 or more, only half of this difference is allowed, in arriving at the Soft Cap Index. Perforce the Soft Cap Index can never exceed the E20.

If the E20 - Anchor is greater than 7.0, then there is a Hard Cap value of Anchor + 5.

Looking at another part of the sample, below, one can see on the highlighted row that
1. E20 - Anchor is 10.10
2. So the Soft Cap Index is 23.20 + 3 + 0.5 * (10.10 - 3) = 29.75.

Similarly for the Hard Cap Index, since E20 - Anchor is greater than 7, the Hard Cap Index is
23.2 + 5 = 28.20.

2017-11-21_12-00-44.png (18.97 KiB) Viewed 1307 times

The circularity in DAX occurs because the Hard Cap Index is based on the Anchor, and the Anchor is based on the prior values of the Hard Cap Index.

It is not really circular, due to the word prior above.

In the Sample attached, I set up a table to be the data model:

2017-11-21_12-20-38.png (9.16 KiB) Viewed 1307 times

I couldn't try to write a DAX measure for the Soft or Hard Cap Indices without one for the Anchor. So I set up a temporary measure Anchor1, based not on the minimum of the Hard Cap index, but temporarily on the E20:

Anchor1

I could then write Temporary SoftCapIndex1 and HardCapIndex1 in terms of Anchor1.

SoftCapIndex1

and HardCapIndex1

I then set up Anchor2 to refer to HardCapIndex1 instead of E20: (I was getting closer to what I wanted, namely an Anchor related to the correct Hard Cap Index, but it was not yet available.)

Anchor2

I could the write measures SoftCap2 and HardCap2 based on Anchor2.

Softcap2

HardCap2

The last piece of the puzzle would be to edit Anchor 2 so that it referred to HardCap2

Anchor2 Revised

And therein lies the problem.

The editor advises that there are no errors in the formula, but when I say OK, I get:

A circular dependency was detected: 'Table3'[Anchor2],'Table3'[HardCap2],'Table3'[Anchor2].

I agree that it is an apparent circular dependency, but was hoping that by using MINX, rather than MIN, the fact that it is not circular at the row by row level may provide a solution.

Any help would be greatly appreciated.

(I seem to have reached the limit on attached files? Three? I will try to attach the sample spreadsheet at a second post.)
Thanks
Michael Maher

mmaher
Posts: 89
Joined: Mon Sep 08, 2014 12:00 pm

### Re: Circular Reference

Sample file attached.
Attachments
Hard Cap Sample.xlsx
Thanks
Michael Maher

MattAllington
Posts: 934
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Circular Reference

Well it's a lot to try to take in. But from what I can see, [Anchor 2] is being referenced inside E20-Anchor2 and then you are trying to add it again into a downstream measure. Maybe you can just duplicate the measure Anchor 2 and use the same formula. I don't know if that would work or not. Another approach is to write Anchor2 as a Calc Column so it is pre-calculated.

These are my best 2 guesses
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

mmaher
Posts: 89
Joined: Mon Sep 08, 2014 12:00 pm

### Re: Circular Reference

Thanks for the suggestion Matt.

I have been trying to implement the calculated column route, without success.

If I put the Anchor1 formula into a column:

I don't get the correct Anchor Values like I do as a measure, rather I get the values of [E20].

2017-12-12_5-43-36.png (32.03 KiB) Viewed 1214 times

For example, the value of the anchor on Row 6 should have bee the prior minimum, 20.7. And on Rows 9-10, the Anchor should have been 20.4.

I tried using MAX instead on MAXX, but that gave an error for every value.
Thanks
Michael Maher

MattAllington
Posts: 934
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Circular Reference

I don't know which table has your formula, and as I said previously (I think) is that it is hard for me to help because I don't understand all the background and the effort for me to come up to speed is high. So my approach to this is to try to guide you to help yourself.

The difference between a measure and a calc column is a calc column has a row context. Any measure inside the calc column will therefore create context transition. Is this what you want? I doubt it.

I suggest now you take a look at my blog article here https://exceleratorbi.com.au/how-to-sol ... x-problem/ Try to start again in a calculated column and think through the filtering process. Write simple formulas to start with that show interim results that you can verify are working before moving on to something more complex and complete. Also aim to work in a single calculated column, not multiple columns that refer to each other - the later will also likely create circular references.
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/