Power BI Relationships - Can't determine the relationships between two or more fields

Adam Parker 0
Viewed 31

As part of our app we have an 'App Owns Data' Power BI embedded scenario. We have written a model with the intention that end users can connect to the Power BI model and write their own simple reports based on said model. 99% of the reports will be simple lists and the model is quite large, with a number of relationships - not your average star schema (we're basically trying to have it work like the old semantic models from SSRS).

It's mostly working well apart from when building a report that joins 4+ tables/views together. An example of our (very) cut down model is below...

Relationships

Users can build reports from fields in Occupancy, Property, Occupancy Person and Person tables, however, as soon as I add fields in from Person Contacts I get the following error

Error

There is obviously something I'm missing as back in SSRS this relationship would have been fine. How can I make it so that users will be able to build reports linking all these entities together?

powerbi-embedded powerbi
1 Answers

Just keep in mind, that all links "one to many" DAX engine converts to SQL LEFT JOIN. In your model you have multiple contacts for each Pearson, it means that DAX engine in fact converts Person Contacts table to a following table:

SELECT * 
  FROM [PERSON CONTACTS]
  LEFT JOIN [PERSON]
  ON [PERSON CONTACTS].[Key]=[PERSON].[Key]

And that's it.

So, [PERSON CONTACTS] has link only with [PERSON] and only in one way. [PERSON CONTACTS] has no relations to any other table.

meanwhile [Occupancy] has Left join with all other tables except [PERSON CONTACTS].

This is the reason why you get this error.

It can be several solutions. You can play with additional key fields and bothside links or DAX.

userLOTh10
Related