Avoiding Mulit to Multi relationship

[StuartFlint]

After watching a lot of the videos, i’d really like to embrace Sam’s recommendation on how to layout the tables, avoiding bidirectional and having waterfall layout.

I have some data

• Staff table (Dim) 1 row per Staff member

• Team table (Dim) 1 row per Team member

• Dates table (Dim) 1 row per Date

• Staff to Team Mapping table - contains which Team a staff member was in on a given day. I’m assuming this is a fact table, where the three columns link to the above dimensions

• Task table (Dim) - 1 row per task

• Task Staff mapping table - this has 1 row per task and contains the TaskID, the StaffID and Date so we know which Staff member was working on the Task each day

• Client table (Dim) - 1 row per Client

• Task Client mapping table - this has a row for each task to client combination. This does not contain the date. A task can have many clients and a client can have many tasks. I am assuming this is a fact table.

This would mean 3 fact tables

My Question:
If i set the model up like that my issues includes the fact that there is no link between staff and client, e.g. if i want metrics on what staff and teams have been working on my top client then i dont think my model would support it.

What I am looking for: Maybe its a video or somewhere an example like this has been discussed, so i can review in relation to my issue. My issue is somewhat more complex than this theoretical example.

[Heather]

some good techniques have been discussed in this forum post - please see if one of these solutions can help.

If not, then a mock up of your model might be needed to better understand your specific use-case.

Avoiding a Many To Many Relationship

I have 10 separate databases- one for each Company. Each Company has branches and departments. Each Company(DB) has a BranchTable, a Department table and a Transaction Table. In the attached pbix file I have a simple example for Branches derived from tables in Excel. I have established a relationship between the two tables for Branch Code - however, it is a many to many relationship. While a newbie to PowerBI, I remember Sam saying that many-to-many relationships are / should be the exceptio…

[Melissa]

In addition to the suggestions already made by @Heather here’s another thought

@StuartFlint

Staff to Team Mapping table - contains which Team a staff member was in on a given day. I’m assuming this is a fact table

I would consider that to be a slowly changing dimension, type 2 is commonly used which means you include an idenifier/flag for the ‘current’ value.

.

Here’re some links to datamodelling related content.

2020 10 24 16 00 Data Modeling Solutions for Challenging BI Data Modeling Problems

Power BI Data Modelling - Marco Russo

Header-detail vs star schema models in Tabular and Power BI

Mastering Power BI Data Modeling: Foundational Best Practices for Optimal Insights

Microsoft Power BI: The Do’s and Don’ts of Power BI Relationships - BRK3019

Slowly Changing Dimensions in Power BI

[EnterpriseDNA]

Hi @StuartFlint did the response provided by the experts above help you solve 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.

[EnterpriseDNA]

Hi @StuartFlint, we’ve noticed that no response has been received from you since October 15.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @StuartFlint, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.