Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jeffprandall
Helper I
Helper I

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 NameDateTotal Hours
A12341/1/20221000
A12341/2/2022500
B23451/1/2022600
B23451/2/2022300

 

Incidents Dataset

Job NameDateIncident Type
A12341/2/2022Sprain
B23451/1/2022Sprain
B23451/1/2022Break
B23451/2/2022Twist

 

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 4
Ashish_Mathur
Super User
Super User

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.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

Hi @jeffprandall 

 

Create a Many to Many relationship like this:

VahidDM_0-1641514894436.png

 

 

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:

screenrecording (6).gif

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.