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.
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.
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!