Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
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.
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.
User | Count |
---|---|
120 | |
69 | |
69 | |
57 | |
50 |
User | Count |
---|---|
167 | |
82 | |
68 | |
65 | |
53 |