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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@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?

@Anonymous no worries. Same thing you have to do, make another table of work area with unique values and set relationship between you schedule and actual tables , and everything will flow.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Cross Filtering wasn't enabled Smiley Happy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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