Date-based matrix visualization with data from 2 tables without a Date table

[MattBossert]

I have two main tables.  One with a count of Orders placed by each employee, by month and another with a count of Errors made by each employee, by month.  I'm trying to create a simple Order Accuracy calculation which is simply 1-(Errors / Orders).  This data will be displayed in a Matrix visualization with Months as my column headers and the employee as the row.

My Orders and Errors table cannot be joined directly as the employee is identified differently in each table.  So, I have other tables which contains both identifiers for each employee.  The Orders and Errors tables are joined via these "Headcount" tables.

I've been unable to figure out how to introduce a date table without creating multiple join paths.  So currently, if I create the Matrix visualization with the "Date" field from the Orders table as my columns, the Monthly Order volume data pulled in is correct, but each Month pulls in the YTD Errors for that employee and not a proper monthly breakdown.  If I use the Errors Date field as the column, the problem is the opposite.

Is there a way to introduce a common date table here without creating multiple join paths, or is there a way in DAX to create a measure which will correct for this?

[v-jiascu-msft]

Hi @MattBossert,

Can you share a sample, please? That would be more clear. Please mask the sensitive parts first.

Why is it "creating multiple join paths"?

Best Regards,

[MattBossert]

Hi @v-jiascu-msft,

Thanks for the reply! 

Here's an image of relationships of these tables.  For the sake of simplicity, I've removed other tables in this report which don't have an impact on this issue. 

My "Headcount" table is the central table in this report.  A table of orders by month for each employee are located in "E1 Order Counts".  Errors made by each employee by month are stored in "E1 Cases Against".  The "E1 HC Person Responsible" table is just a helper table to join my Errors table to the Headcount table.

The only two tables which contain dates here are the "E1 Order Counts" table and the "E1 Cases Against" table.  The dates in both tables are in the same format and represent the first day of the month (1/1/2019, 2/1/2019, etc).  If I create a separate Date table to directly join "E1 Order Counts" and "E1 Cases Against", PowerBI will only allow 1 of those joins to be active at a time to avoid having a circular join path between all tables.  If I try and create a join on Date directly between E1 Order Counts and E1 Cases Against, the same problem exists.

When I'm building the Matrix visualization to calculate an employee's Order Accuracy, I need to pull in their order count from the E1 Order Counts table and their error counts from the E1 Cases Againts table.  I'm trying to put this in a simple Matrix visualization with the Date (by month) as column headers.

When I build the visualization, I can pull in the "Date" field from either the Orders table or the Cases Against table.  If I pull the Date field from the Cases Against table, the employee's Cases Against for each month, and YTD are correct, but their Orders pull in the YTD total only and display that YTD total for each individual month, which throws off my calculation.

I know why it's not working, I just don't know how to fix it.  I've tried various combinations of CALCULATE and FILTER to try and return results only where the dates from both tables match, but haven't been able to get that to work.

Appreciate your help!

Matt

[MattBossert · Accepted solution]

I ended up figuring this out, so I wanted to share the solution in case any other PowerBi novices run into the same issue.  The problem is that my main "Headcount" table had some duplicate records which caused a many-to-many relationship with some of the other tables it was joined to.  I cleaned up the "Headcount" table and now it has a 1-to-1 or 1-to-many relationship with all of the tables it's joined too.

Fixing that issue allowed me to introduce a Date table which can then be joined to both my Orders and Errors tables while keeping both joins active.  With both joins active, I was now able to leverage the Date table in my Matrix Visualization and successfully use values from both the Cases Against and Orders table in my formula.  

Here's the final relationship between all of the tables: