Many to Many Join

[jeffprandall]

I have two datasets I want to join so I can use a slicer/filter to show the total number of incidents based compared to the total number of hours on a job.

Hours Dataset

Job Name	Date	Total Hours
A1234	1/1/2022	1000
A1234	1/2/2022	500
B2345	1/1/2022	600
B2345	1/2/2022	300

Incidents Dataset

Job Name	Date	Incident Type
A1234	1/2/2022	Sprain
B2345	1/1/2022	Sprain
B2345	1/1/2022	Break
B2345	1/2/2022	Twist

Ideally if I selected 'A1234' from the Slicer I would see a chart that said 1500 total hours and 1 injury.  And if I selected 'B2345' it would say 900 hours and 3 injuries.

I have tried doing a Many to Many join but the visuals end up showing blank or just one half of the data.

[Ashish_Mathur]

Hi,

Create a Job Name table and build a relationship from the Job Name table of both tables to the new Job name table created.  Build your slicer from the new job names table and select A1234 there.  Write these measures:

Hours = sum(Hours[hours])

Count = countrows(Incidents)

Hope this helps.  

[jeffprandall]

I ended up creating a Date Table and then joining both the tables based on dates to those and it worked before I saw this.

[VahidDM]

Hi @jeffprandall 

Create a Many to Many relationship like this:

then add Job name from Hours or Incidents to the slicer, then add total hours to the table with sum aggregation and Incident Type with Count aggregation.
the output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[jeffprandall]

I did Many to Many joins on the both the Job Name AND Date fields.

I put a slicer on the page that gets the Year from the Hours Dataset.  It does a great job of filtering the Hours by Year but it's pulling random data from the Incidents dataset.  I can't see a pattern for which records its pulling from the Incidents dataset.