Data model relationship creates cartesian product

[MattRasmussen]

I am trying to get the total number of hours worked on projects by departement.  Multiple departments can work on the same project so there are many examples of the project being listed for each department.  Power BI is giving the total number of hours for the project on all rows instead of the number for that department.

Here is a portion of the data model showing the three tables needed in a report.  Notice that each table has a one to many relationship between them, not a many to many relationship.  I have a DAX column on two tables that combines the project number and department, PrjID_dept, that is used in the highlighted relationship because you can't create a relationship of multiple columns.  I thought that would allow the correct department total to be displayed; it doesn't.

The screenshot below shows the query results with my notes to the side to explain.  I apologise for the small text to fit!  Click the picture to open the fullsize version:

Before I used the DAX combination column PrjID_dept as the relationship key, the relationship key was on Project Number: Project ID between the tables and I had the repeated value/cartesian product.  That's when I added the DAX column but to no effect.

What needs to change in the data model to show the correct hours for the respective departments?

[v-yalanwu-msft]

Hi, @MattRasmussen ,

Sorry, can you share the result display that you want to output? I can't reproduce your model. If could, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MattRasmussen · Accepted solution]

Our data model is separate from the reports and housed on the Power BI Service so I prepared a file from scratch to share.  However, the report worked as intended!  So I deleted all of the relationships in the data model, saved the pbix file, recreated the relationships, then opened the report and found it worked right.

I don't understand why it didn't work correctly to begin with since the relationships were recreated with the same definitions but I'll take the final outcome.  I guess the resolution is to delete the relationships and try them again.

[MattRasmussen]

As proof that the relationship I defined works, I exported my two tables to Access, then in Access I joined the tables, used the query wizard to make the same query in my screenshot above, and it gave me the results I wanted in less than 10 minutes.

Are there other settings in Power BI that I'm not aware of that are causing my query to show the wrong amounts?  (I've only been using it for six months.)  Thanks to anyone who can shed some light on this.