Circular Reference

Question

Hi guys

I've made two calculated columns keys to join tables on different granularities.  This works in Excel 2013 but unfortunately PowerBI is reporting a circular reference.  

My fact table is by ID and daily date whereas the lookup table is by ID and period end date. These need to be joined by store_ID & Period_End_Date.

Fact_Table (Daily_Date | Store_ID | $) 

ID_AND_PERIOD_END_KEY = 
INT ( [Store_ID] & 
       INT (
            CALCULATE (
                MIN ( Lookup_Table[Period_End_Date] ),
                FILTER (
                    Lookup_Table,
                    Lookup_Table[Previous_Period_End_Date] <= Fact_Table[Daily_Date] &&
                    Lookup_Table[Period_End_Date] >= Fact_Table[Daily_Date] &&
                    Lookup_Table[Store_ID] = Fact_Table[Store_ID]
                )
            )
        )
)

Lookup_Table (Previous_Period_End_Date | Period_End_Date | Store_ID | ... other important month-end fields)

ID_AND_PERIOD_END_KEY =

INT([Store_ID] & INT([Period_End_Date]))

When joined: 

Failed to save modifications to the Server. Error returned: 'A circular dependency was detected: Fact_Table[ID_AND_PERIOD_END_KEY], 7bd30781-b114-4454-95ea-fb05ebb44ebe, Fact_Table[ID_AND_PERIOD_END_KEY].

Suggestions?

Answer 1

Before trying to dive into the logic you're implementing here, I'd take a stab at changing the relationship type in Power BI. Power BI Desktop by default creates a different type of relationship than the one that is created by Excel 2013. You want a one-way relationship. Double click on the relationship line in the model view in Power BI and then use Advanced Options to mark the direction as one-way. See picture below:

---

GNet Group BI Consultant

Answer 2

Thanks Greg.  

Unfortunately I can't establish a relationship to begin with due to the circular referencing error. When I first create the calculated column in my fact table there is no join therefore no circular reference, i.e. CALCULATE operates purely off the FILTER logic.  When a join is attempted by the engine, it looks like the CALCULATE tries to use the join to calculate itself, i.e. a circular reference.

It's almost as if the PowerBI engine "compiles" itself in a different order than the Excel 2013 one. 

If I attempt to create the relationship using the Edit Relationship menu I receive the below error:
"The expression referenced column Fact_Table[ID_AND_PERIOD_END_KEY] which does not hold any data because it needs to be recalculated or refreshed."

I've tried recalculating and refreshing.

Answer 3

Okay, I guess I don't get to be lazy here, but also more interesting that it won't work with a one-way relationship. I'll probably explore that later.

For now, I've got some questions. First, the modelling question. Why can't you have a separate store dimension and date dimension? Why do these need to be combined in a single lookup table? This would be trivial with two dimensions instead of one.

The only answer that comes to mind is that each store can have a different fiscal calendar, so you can't use just one date dimension.

That being said, I think the solution is still better modeling. First of all, assigning keys is really an ETL function, not a model level function. Can you utilize a SQL query or a tool like Power Query to assign your keys before bringing the data into the model?

If you can't, then I'd suggest you go whole-hog on combining your date dimension and store dimension, rather than this store and period end date thing - that's where your complexity is.

What I propose is exploding your Lookup_Table to have one entry per store per date. Then you can replace your entire formula with a call to LOOKUPVALUE():

ID_AND_PERIOD_END_KEY=
[Store_ID] &
    LOOKUPVALUE(
        Lookup_Table[Period_End_Date]
        ,Lookup_Table[Daily_Date]
        ,Fact_Table[Daily_Date]
        ,Lookup_Table[Store_ID]
        ,Fact_Table[Sore_ID]
    )

This should be completely unaffected by any relationship whatsoever.

---

GNet Group BI Consultant

Answer 4

Understood, thanks Greg.  

I totally agree with all the points you bought up.  Also, you're correct regarding the different fiscal calendar -not only do they vary between stores but they also change over time per store.

I'll try merging, sorting and then filling down in Power Query.  I think that'll do the trick.

Answer 5

I do not envy you a model with a fiscal calendar as a slowly changing dimension. At that point, I think it's appropriate to file a bug against the business process.

---

GNet Group BI Consultant

Answer 6

Just reporting back for anyone interested:

Solution 1:

Import the lookup table twice and perform the CALCULATE on that.

Solution 2:

Perform the calculations in Power Query.  In my case, I found it too slow to load (filtering + fill down was a killer on 5mil+ rows) but it has the great benefit of being included in the compression phase when the PowerPivot engine compiles.

Solution 3 (the one I adopted):

Use a function that won't rely on the join to calculate itself:

=MINX ( 
    FILTER ( 
        Lookup_Table, 
Lookup_Table[Previous_Period_End_Date] <= Fact_Table[Daily_Date] &&
Lookup_Table[Period_End_Date] >= Fact_Table[Daily_Date] &&
Lookup_Table[Store_ID] = Fact_Table[Store_ID]
    ), 
    Lookup_Table[Period_End_Date] 
)

Answer 7

Just a note on compression. A calculated column is evaluated once, at refresh time, and is compressed similarly to "native" columns.

---

GNet Group BI Consultant