DAX modelling circular reference error
I’ve been working on a new power BI model this week, with the intent of showing historical attributes of work done over time. So I end up with table OrdersPFMHistory which contains a key that indicates a state at a point in time. The Orders table, has that same key. The intent is to use RELATED() to identify if the historical key exists.
However, every time I create that relationship, it would seem to work until I switch to the graph view. This is the error I would then receive.
The model contains an unexpected cycle among associations with to-one cardinality.
Invocation Stack Trace:
Mon Nov 23 2015 12:02:56 GMT-0700 (Mountain Standard Time)
After much noodling around, I discovered that it was not complaining about a circular reference, but rather about the way that relationship could be filtered. It turns out that powerBI assumes that you’ll want to filter from both sides of the relationship (this seems to be the default). Setting the cross filter direction to “Single” alleviates this issue. Technically, it seems that this should have worked as “both”, and that this is just a bug.
I’m posting about this, as the solution is not overly obvious. Someone else is likely to run into this issue.