CODEWITHSUNDEEP
powerbipowerbi-embedded
Adam Parker
Adam Parker

Reputation: 156

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

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?

Upvotes: 1

Views: 1858

Answers (2)

Mike Honey
Mike Honey

Reputation: 15037

If you just need one column or two from 'PERSON CONTACTS', then I would resolve this with a DAX measure to concatenate the multiple 'PERSON CONTACTS' records, along these lines:

All Contacts =
CONCATENATEX (
    DISTINCT ( 'PERSON CONTACTS'[CONTACT DETAIL] ),
    'PERSON CONTACTS'[CONTACT DETAIL],
    ", ",
    'PERSON CONTACTS'[CONTACT DETAIL]
)

This will keep returning a single row for each Person, with their Contact Details concatenated into one cell.

If however, you need multiple columns from 'PERSON CONTACTS', I would turn its relationship with 'PERSON' into one-to-one. This might require filtering the 'PERSON CONTACTS' data in the Query layer, e.g. only the "Primary" or "Most recent" record for each 'PERSON'

Upvotes: 0

Mik
Mik

Reputation: 2103

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.

Upvotes: 2

Related Questions