Relationship Ambiguity - trying to replicate a call logging system in reports.

[skipfeeney]

Hi,

I am new to PowerBI (I am SQL / SSRS developer)  and I am struggling to work out how to do simple relationships.  I am replicating a decommissioned call logging system into a report.  The call logging system has Incidents which are linked to tasks.  However both sets of records are linked to Attachments and Journal  entries.  I want to be able to drill down to the incident detail and see a list of attachments, journal entries and tasks.    For Attachements and Journal entries its working fine but when I try and join Incidents and Tasks together I get the ambiguity error. I have gone through several youtube videos and the only viable option seems to be to copy one of the tables and link it directly - is this the only option?  The tables are larger 500,000+ rows.

Incident

- Links Attachments

- Links Journal Entries

- Links Tasks

Tasks

- Links Attachments

- Links Journal Entries

I understand the concept of ambiguity - that by linking Tasks with Incidents its essentially joining Attachments and Journal entries twice and filtering data would not know which route to take but how do I get around that.

Thanks

[Idrissshatila]

Hello @skipfeeney ,

To get around it, you should build a star schema data model where you have common dimension tables that then link to all you fact tables.

check it here https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[skipfeeney]

Thanks but what happens in my instance if you can't build a star schema model? The data won't fit that model.   I have fact tables linked to other fact tables.   The data is coming from a third party commercial system so I have no control over it.