Can't determine relationships between the fields

[nchamilton162]

Good morning, 

I am struggling with this error message. I have a simple three table model. I have all three tables connected via a field called projectid. A project from the project table can have 4 phase records connected by projectid. A Project can have multiple users assigned by projectid. When I attempt to pull a project and project phase into my table visual it works fine. As soon as I add a user to my table I get the dreaded "Can't determine relationships between the fields. See details. Can someone please help. I've read several articles and attmempted to modify data types, I've verified through SQL that there is in fact a relationship between all 3 tables and it works fine in sql. I'm fairly new to Power BI so I assume I'm missing something, I just can't figure out what I'm doing wrong. I have attempted all variations of cross filtering.

[nchamilton162]

I don't necessarily want a measure. What you're saying makes sense regarding it not being able to determine which phase the user belongs to.

[SamWiseOwl]

If you don't care about have the users on a seperate row you could do this:
Users In Project =
ConcatenateX( UserTable, [User Column],", ")

That will show them in a nice comma seperated list next to the phase.

[SamWiseOwl · Accepted solution]

Hi @nchamilton162 

The issue is how the filtering occurs.

Project filters Phase down to the matching project,

Project can also filter User down.

This means you could display Project, Phase and an aggregate of User.

However the User table has no information about which Phase each belongs to so it will count all of them.

Effectively you have two different granularities Phase and User. PBI doesn't know how to match them up.

How do you match them up in SQL? A cross join?

What output are you looking to achieve?

[johnt75]

When you add the user to table visual is there already a measure in the visual ? This error can sometimes happen when there is no measure.

If there is a measure, can you post the definition of it ?