Forum Discussion

JustDavid's avatar
JustDavid
Icon for Helper V rankHelper V
2 days ago

Unable to create relationship

Hello PowerBI and DAX gurus,

My end goal is to create a measure where I want to get the SUM of ([Hourly Rate] x [Number of Hours]) of each employee who works in that LOB in a given month.

However, before I can even reach this goal, I am stuck in creating the relationship.

As screenshot show below, I am trying to create a relationship from LOB[EmpName] to Payroll[EmpName] and Timesheets[EmpName], but unable to (2nd screenshot below shows the "error" message)

Thus may I know what's the solution so that at the end, I'm able to create a measure where based on the LOB (whether it's from [Shipped Month End Order], [dimLOB] or [LOB] table), I am able to get the correct calculation of salary paid, which comes from Payroll and Timesheets by employee and LOB.

A little explanation of the tables

Shipped Month End Order - This is a fact table where it shows Line of Business (LOB) code and customer

Payroll - This is a fact table where it shows the Employees with the Hour Types (Regular, Vacation, Sick, Stat Holiday, Overtime etc) in a given period (not date but period. A period is 1 week of calendar date). I have created a column 'EOM Date' in order to capture the total number of hours at the end of the month

Timesheet - This is a fact table where it shows the Employees with their hourly rate by each date

LOB - This is a fact table where it shows the Employees and it's allocations who work in a given LOB. i.e. Employee A, B, C and D works at LOB 1000 at 10%, 20%, 30% and 40%. This means that Employee A works only 10% for LOB 1000.

Now assuming if model and relationship works, what I had in mind of what the measure to calculate is.

  1. Determine the LOB (i.e. from [dimLOB] or [LOB] table) and Date (from dimDate)
  2. Find out who are the employees who work in that LOB and in that month of date
  3. Get the Hourly Rate of those employees in that month of date
  4. Get the Number of Hours worked for those employees in that month of date
  5. Multiply the Hourly Rate and Number of Hours and SUM them for each employees in that month of date

 Note here that LOB are data shown in the visualization table down the row

Note here that Date is "captured" via Slicer

I have shared the URL link to my google drive and hopefully you can download it. Note that if you were to go to Power Query, it'll fail as source data is from both my local drive and sharepoint.

Thank you in advanced for your help

Power BI file

Need Help
Show and Tell

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 days ago

    Hi,

    Create 2 more tables (each with a single column and no duplicates) - LOB and Empname.  Remove the bidirectional relationship.  Create the following relationships:

    1. From LOB, payroll and Timesheets table to the Emp table
    2. From the Shipped month end table and old LOB table to the new LOB table
    • JustDavid's avatar
      JustDavid
      Icon for Helper V rankHelper V
      2 days ago

      Hi Ashish_Mathur,

      Thank you for your reply.

      In my Power Bi sample, I do have a dimLOB, where it list the "unique" LOB and only 1 column. In this dimLOB table, I have a "blank" LOB, and this is needed.

      I have tried to create a relationship with this dimLOB table, and that it'll give me a many to many relationship because of this "blank".

      From your suggestion, would this still work?

      Also, need to clarify a few things.

      On point#1, can I assume you meant that newLOB table (which have 2 columns - LOB (duplicates) and Employee (unique) ) connects to Emp table (which only have 1 column Employee Name (unique))  and Payroll (Employee with duplicate values) and Timesheets (Employee with duplicate values) connects to Emp Table (Employee Name which only have unique values)?

      On point#2, Shipped month end table (Line of Business) connects to oldLOB table (LOB) and connects to newLOB table (LOB) and that these are many - many - many relationship (yes 3 many)?

      Thank you again for your help and clarification

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 hours ago

        Hi.  The dimLOB tables cannot/should not contain duplicates/blanks.

    • JustDavid's avatar
      JustDavid
      Icon for Helper V rankHelper V
      2 days ago

      For some reason my reply was not accepted (after I've written a long reply).

      Am not following, you mentioned to create LOB and EmpName table in which both of them are single column without duplicates.

      So I assume that LOB will have 1 column, name colLOB  and  EmpName will have 1 column, name colEmployeeName.

      What I don't understand here is that you wrote in point#1, where you say to create relationship from the new LOB table to the new EmpName table. But there is no "relationship" between the new LOB table (values such as 1000, 2000 etc) and EmpName table (values such as Jane Smith, John Doe etc).

      Lastly, in my sample power bi file, I have a dimLOB, which is exactly the same as your mentioned new LOB table. Note here that in this dimLOB, it is unique, and one of the values is "blank" (without double quote). When I try to create a relationship from dimLOB to my Shipped Month End table, it creates a Many to Many relationship, not 1 to many.