PBIX Model Assistance - Relationships

[tdoolittle]

I have a standard star schema model with dimensions and facts, a typical invoice setup. A report requirement is to integrate a reference table that relates to two separate dimensions on opposite ends of the model that do not directly relate to one another. I want to keep the model intact, and find out how to properly integrate the reference table 'TableToFix' so that it can work in the context of the visuals I have created.

This is a very simple task in SQL with JOINs, but I can't figure out how to make it work within a PBI semantic model where each table is separated into a star schema.

Here is a sample PBIX:

Dimensional Model Assistance.pbix - Google Drive 

[v-yifanw-msft]

Hi @tdoolittle ,

May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please feel free to ask us a question.

Best Regards,

Ada Wang

[tdoolittle]

Hi, no, unfortunately it's still unresolved. The original issue still remains.

[Ashish_Mathur]

Hi,

The AR_Customer table being a DIM table should not have any duplicates in the CustomerNo column.  Once you have only unique entries in that column, the relationship from the TableToFix table to the AR_Customer table can be changed to Many to One.

[tdoolittle]

Hi, unfortunately in the Sage system the primary key is defined as both an ARDivisionNo + CustomerNo.

Sage AR_Customer File Layout 

Perhaps I can try to create a table with unique CustomerNo values that sits between AR_Customer and TableToFix. And do the same for the relationship of TableToFix and CI_Item. Will report back.

[tdoolittle]

Nope, looks like that can't be a solution either. I added 'Bridge_Customer' in between 'AR_Customer' and 'TableToFix', and then 'Bridge_Service' in between 'CI_Item' and 'TableToFix', which removed the Many-to-Many on both sides. But then when creating the relationship, it brings an error when you connect the full circle:

There are ambiguous paths between 'AR_Invoice' and 'TableToFix':

'AR_Invoice'->'AR_Customer'->'Bridge_Customer'->'TableToFix' and 'AR_Invoice'->'AR_InvoiceLines'->'CI_Item'->'Bridge_Service'->'TableToFix'

I'm thinking due to the nature of this circular relationship, it might not be possible to implement something like 'TableToFix' in this model, since it needs to relate to opposite ends of the star, 'AR_Customer' and 'CI_Item'.

'AR_Customer' [1->*] 'AR_Invoice' [1->*] 'AR_InvoiceLines' [*<-1] 'CI_Item'