cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MattRasmussen
Helper I
Helper I

Data model relationship creates cartesian product

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.

PBIX project status and time data model.png

 

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:

PBIX time queries repeating total instead of detail subtotal.png

 

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?

1 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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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.

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
Helper I
Helper I

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.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors