Forum Discussion
Many to Many Join
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.
4 Replies
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/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.
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.
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.
