Data Modeling w/ Multiple Facts

[ElvirBotic]

Hello, 

I am a bit stuck and need some guidance with a data modeling issue I am facing. Recently, users wanted to add an additional table to support some reporting efforts and that table when building a relationship shows a cardinality error and will only accept a M:M relationship, which I do not want to have. 

Background on the current data model. I current have a single fact table that is called "Projects" it contains all of our projects at the company. The new table I want to add is called "Project Fees" we have certain peojects that collect a fee monthly, annually, weekly, etc. The "Project Fee" table contains the project number many times and when I tried building a relationship between "Project" and "Project Fees" I want it to be 1:M. All I needfrom the "Project Fee" is the fee amount and when it was paid/received. Obvisouly, this is not working and I began exploring a bridge table. 

I duplicate the "Project Fee" table trim the key column, remove other columns, and remove duplicates. I try testing building a relationship between "Project Fees" and the bridge table with all of the unique IDs from "Project Fees" and when creating the relationship Power BI tells me the cardinality only works for M:M. Is there a better approach to using these two tables to be able to see when the fees were paid by Project ID from the "Projects" table? Or am I completely going about it the wrong way?  

[ElvirBotic · Accepted solution]

Just as an update. I found my issue. I no longer need a bridge table because I corrected some data in our source. What was happening was a specific user kept using a placeholder ID that was the same and I told them that they could not do that. Once that data was corrected and removed the relationship between the two tables worked as intended. 

[CoreyP]

You're on the right track. You do want to create a "projects" dimension table that contains a distinct list of all the project ID's from both fact tables. Then you can create a 1:* relationship from this dimension to both fact tables.

[Idrissshatila]

Hello @ElvirBotic ,

What Should work is to have a table that has unique project IDs  that is then linked to both tables and then view the values by this new table.

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 💡

[ElvirBotic]

I have created a bridge table and removed all duplicates from the table, but filter propigation is not working. Current relationships are... 

"Project Fee" M:1 "bridge Table" than...
"Bridge Table" M:M "Project" 

I do not see why it is doing this when I am removing all blank rows and removing duplicates.