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?
