Forum Discussion
Measure Error With Many to Many Table Relationships?
Hello fellow PowerBI users! I am completely new to PowerBI and DAX programming, and I would like to know if many to many relationships are causing an issue.
I have two separate tables in my model. One table that has hours scheduled for a particular employee with the key being the employee number. The other table has hours actual worked (retrieved by time punch data) with the employee number as well. Since both of these tables consist of many employee numbers, I am assuming this is a bad practice, and is ultimately causing my error.
Which brings me to the actual error. I am trying to compute productivity i.e. the total number of hours worked divided by hours scheduled. I would then like to filter on dates,different areas, shifts, etc. I am just creating two separate measures that sum up the hours (worked and scheduled). From there, I am creating another measure that divides those two measures to get productivity as a percent. This number is completely off when I try to filter on workcenters for example.
Does anyone have any insight on this? Am I going about it completely wrong or is the many to many relationship causing an issue? I appreciate any sort of advice,criticism, etc as I am just starting with PowerBI.
Anonymous calculation looks correct, wihtout knowing underneath data, it is very hard to say why you will get weird result.
You want to drop all your measures in a table visual and work areas and see if you are getting correct number of sum of hours.
8 Replies
Anonymous what you need is seperate dimension to make it easy.
- add employee table (unique employee), and set relation between this table and other two tables. (Don't set relationship between those two tables)
- add date dimension to set relationship similar to above
for any visualization, use employee and date from date dimension and measures you created will work nicely.
parry2k Thank you for the response, I have already tried this, but maybe I'm still missing something. Can you explain what you mean by "for any visualization, use employee and date from date dimension?" Is the a parameter I need to use in the visualization to tell it to use those keys? Thanks a lot!
Anonymous let's assume you have currently two tables, scheduled hours and actual hours and both contains following columns
Employee Id
Date
Hours
So what you need to do is add two more tables
- Employee Table and it will contain employee id and employee name, and set relationship on employee id from this employee table with scheduled and actual hours tables.
similarly you will add Date Table (there are many posts on how to add date dimension), and set the relationship
There is no direct relationship between schedule and actual hours tables
now add a a table visual, drop employee name from employee table, schedule and actual hours from respective table and you will see everything will line up and you can also date from date table and it will give you breakup by employee and date.
Hope it helps.