Connecting Multiple Tables to The Same ID Column

Power BI Data Model
1

Hi, I am about to build a data model connecting one Lookup table to three different Fact production tables with the same MasterData_ID column in each. In planning the relationship, am I able to link the MasterData_ID column of the Lookup table to each of the three respective production tables’ MasterData_ID columns so they they all relate (talk) to each other?

In other words, can I run ‘three’ one-to-many relationships from the one Lookup table (MasterData_ID) to the three production tables (MasterData_ID)? Would this be a logical configuration? Thanks.

PBI Linking

1 Like
2

Hi @Dplex,

That’s exactly how it works your Dimension/Lookup table filters will propagate through the 1:* relationship to all associated fact tables. A best practice is to hide all Key fields in those fact tables so they can’t be selected by mistake.

@Greg created a series on best practices, this one refers to modelling. I’d suggest to check out the entire series you can find it in the Best Practices playlist on the eDNA YouTube Channel.

As well as this structured course, it has several chapters on modelling.

I hope this is helpful

1 Like
3

Hi @Dplex, did the response provided by @Melissa help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hi @Dplex, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

5

Hi Melissa, Kind thanks for your reply. I appreciate that.

Unfortunately, the company data I work with is quite problematic So problematic that I have decided to recreate our company files substituted with dummy data to recreate the issues I am having. Then I can upload the PPIX file here for feedback. That’s the only way I can properly present the issues I am facing.

The diagram you have illustrated above is exactly an example of what I have now. Two fact tables are displaying data to a Matrix table while the third measure isn’t. And all three fact tables are connected (one to many) from the lookup table. So this anamoly is quite frustrating.

Anyway, I expect to have the actual files recreated next week and be able to post them here for review.

Kind thanks.

And, I also enjoyed your M videos too. Cheers for deep diving into the M world.

6

Hello @Dplex, and thank you for adding feedback to the post with a solution from almost a year ago.

Within the context of the Forum, members tend not to follow posts marked as having a Solution, especially as the Post is aging towards a year.

Would you please close this post, and open a fresh post at a later date as you mentioned, within that one reference this post as added value.