Simple Relationship Understanding

[Anonymous]

Hi there,

I completely reworked my relationship tables today to clean them up.  I'm overall happy with how things are looking now.  However, peeking at this cut-down relationship table, can someone please walk me through why when creating a column in Forecasted Revenue it errors saying Stage (in Projects) is not found?  I'm being forced to add the inactive relationship between the two tables (on ProjectID) and determine the column with UseRelationship.

None of these pictures tables are actually joined by ProjectID other than Project Summary/Project.  The rest of the relationship are based off different keys such as ProjectEmployee or ProjectDayEmployee (such as revenue).  However, without using "userelationship" I can display the Revenue and Stage on the same report table, no problem.  Thanks!

[v-lili6-msft]

hi @Anonymous 

Since you have created two relationship between Projects and Project summary,  Project summary and Forecasted Revenue, and the two relationships are all "Both", it likes you have created a reltionship between Forecasted Revenue and Project tables, so you don't need to create another relationship between them(Forecasted Revenue and Project), you could only create other inactive relationships.

Regards,

Lin

[edhans]

The tables have to be related to each other or through other relationships to reference each other, active or inactive.

Just a suggestion - you have a lot of bi-directional relationships. That can cause issues. Take a look at this article. I only create them as a last LAST resort to a problem. I use the CROSSFILTER() modifier in CALCULATE() if I need to have a measure be bi-directional without impacting the model.

[Anonymous]

I have heard about the issues with bidirectional.  Great link though.

Please correct me if I'm wrong, but my understanding of bidrectional relationships is they cause trouble when you have multiple tables using them together (so as to create a multi path issue or circular relationship).  Other than the inactive relationship (that I was forced to do), all of my tables are joined by a single relationship path directly to Project Summary.

I'm doing this so I can filter various data points for the same employee/project/day such as summing timesheet details and forecasted projects...

There is where I'm a little confused, forecasted Revenue should have a link (and only one link) to Projects[Stage] (through Project Summary).  Is that not correct?!?  DAX is saying it can't find the column at all without using that inactive relationship?!?!?

[edhans]

No, there are other probems with bi-directional relationships. Microsoft themselves recommend minimizing their usage. They impact the query performance as well. Best practice is, only use it if you know EXACTLY what you are doing and there is no other way. Same with many-to-many relationships.

As to your model, I cannot say for sure. I have seen weird things in models with bi-directional relationships that don't have ambiguity that suddenly work when you disable it. Had a user in here with a single bi-directional relationship that was causing a slicer issue. Turned it off and it worked fine. I'd go about removing all of those and then continue with your model building.