How to link multiple tables ?

[chandiaz]

Hello, 

I have three files. 

1.  Transactions file [VL06F Reports] - Captures transactions performed by individual employees by date, [ Each Individual user has unique SAP User ID]

2.  Attendance and hours [ Kronos_Hours] - contains hours worked by each employee by date. [ There is no reference to SAP User ID, but has an employee number ( Person Number)]

I need to calculate the productivity [ number of transactions performed by employee per hour]. As the above two files do not unique values I have created calendar table with unique dates and have linked both transactions file and employee attendance file to this. 

In addition, I have created User list which contains SAP User ID and Person Number. 

I have tried to establish relationships between tables as per below. 

Although I could create these relationships in power pivot, I couldn't establish these relationships in power bi. It gives below error message.  

Appreciate your suggestions and alternative simple approach that i can have 

thanks in advance. 

[P3Tom]

You have shown an image of your Excel data model, but not of your Power BI data model, and I believe, once you get down to direction of filter flow with the relationships, they are not going to be the same.

Power BI defaults to bi-directional relationships.  If you only use single direction relationships in Power BI with filter flowing from the one side to the many side of the relationship, you will find you can create the same relationships as in Excel.

Tom

www.powerpivotpro.com