Unable to create relationship

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

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.

Hi Praful_Potphode​ ,

Thank you for your help and the sample file.

I've checked and realized that you've created a new table call dimLOB_new, in which in this table, you have listed all the LOB, but then, the "blank" is missing, in which it is also one of the "value" of LOB.

If I want to include this "blank" LOB, what's the best alternative in order for it to work? i.e. instead of blank, give a "dummy" value call null etc?

Secondly, I was looking at your measure, the part where you're trying to get the rate, VAR RateThisMonth, I realized that it is taking the average.

This shouldn't take the average, but it should take each individual employee who works in that LOB for the period in question, and get the hourly rate and then multiply the number of hours works. This needs to be done "individually" for each employee as each employee's number of hours worked and hourly rate differs from person to person on each period.

So am not understanding as to why you're taking average.

Again, thank you so much for your help

Salary Paid by LOB =

VAR EmployeesInLOB =

    CALCULATETABLE (

        VALUES ( dimEmployee[EmpName] ),

        LOB          -- inherits whatever LOB is selected via dimLOB

    )

VAR Result =

    SUMX (

        EmployeesInLOB,

        VAR CurrentEmp = dimEmployee[EmpName]

        VAR HoursThisMonth =

            CALCULATE (

                SUM ( Payroll[Hours] ),

                Payroll[EmpName] = CurrentEmp

                -- month filter still comes from dimDate -> Payroll[EOM Date]

            )

        VAR RateThisMonth =

            CALCULATE (

                AVERAGE ( Timesheets[Hourly Rate] ),

                Timesheets[EmpName] = CurrentEmp

                -- month filter still comes from dimDate -> Timesheets[Date]

            )

        RETURN HoursThisMonth * RateThisMonth

    )

RETURN

    Result

Hi maruthisp​ ,

Thank you for your help.

I looked at your pbix file, and I see that you've built a similar model to mine and not using my pbix. Thus it's really difficult to check if result is the same as how I have showed the flow and logic of the calculation.

I did have a detailed look, and I realized that your LOB table do not have "blank" value, in which this "blank" value is considered a value that needed to be considered.

Lastly, your measure that you've created, can you explain where it incorporates to take additional 50% on O/T 1.5 (in your case you've identify as Overtime)?

Salary Paid by LOB =

VAR EmployeeAllocation =

    SUMMARIZE (

        EmployeeLOB,

        EmployeeLOB[EmpName],

        EmployeeLOB[AllocationPct]

    )

RETURN

    SUMX (

        EmployeeAllocation,

        VAR CurrentEmployee =

            EmployeeLOB[EmpName]

        VAR Allocation =

            EmployeeLOB[AllocationPct]

        VAR HoursWorked =

            CALCULATE (

                [Total Hours],

                TREATAS (

                    { CurrentEmployee },

                    DimEmployee[EmpName]

                )

            )

        VAR HourlyRate =

            CALCULATE (

                [Employee Hourly Rate],

                TREATAS (

                    { CurrentEmployee },

                    DimEmployee[EmpName]

                )

            )

        RETURN

            HoursWorked

                * HourlyRate

                * Allocation

    )

LumericVisuals​ Thanks for the reply.

Am not really following with your reply here.

What is it that I'm suppose to do?

Am I suppose to create a dimension of Unique Employees (i.e. dimEmp)? And if it is, what is the relationship that I should create to?

v-saisrao-msft​ No, my issue hasn't been resolved and I have not received any additional reply and/or solution since my last reply back on Sep 17th/18th.