Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tdoolittle
Frequent Visitor

PBIX Model Assistance - Relationships

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 

5 REPLIES 5
Anonymous
Not applicable

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

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

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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'

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.