Forum Discussion
Measure Error With Many to Many Table Relationships?
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.
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.
parry2k I completely understand what you are saying, and it makes total sense. I am still not getting what I want, maybe my logic is completely wrong. Maybe you can provide some guidance or give me an indication of what can be going wrong. The table with the scheduled hours (Table2) can have the employee ID multiple times for the same date as each task the employee does has different amount of scheduled hours. The table with the actual hours (Table1) also contains many employee IDs for one date as employees all punch out on the same day. What I am doing to compute productivity is the following:
mTotalScheduled = SUM(Table2[Scheduled Hours])
mTotalWorked = SUM(Table1[Hours Worked])
mProductivity = DIVIDE([mTotalWorked],[mTotalScheduled])
Is this logic incorrect? I am getting outrageous percentages when I filter by work areas.
Thanks again for your help!
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.
parry2k One last thing (sorry for so many questions). I just checked the measure and the one calculating scheduled hours is filtered correctly on work area, but that might be because work area exists in the same table as scheduled hours. When I try to filter actual hours by work area, I am getting the same value for every one: the sum of all the hours worked in that table. Any pointers here?