creating relationships between tables of dates and names

[dlz217]

I'm working with 4 tables that are part of a payroll report:

- employee names and details

- pay dates

- hourly projects, with pay dates, employee names, and project details

- unit projects, with pay dates, employee names, and project details

I think want to build analtyics around this, specifically a bar chart that you can drill down monthly and see per employee, the two types of projects they worked on side by side: hourly and unit.

In the relationships pane, I've linked One to Many between employee names and both project tables to the names. However, I can't seem to link the pay dates properly. It's saying if I create a One to Many between the Pay Dates table and both projects tables, then I create ambiguity between Pay Dates and Employee Names.

How do I create relationships for both names and dates between these two tables? Thanks.

[v-qiuyu-msft]

Hi @dlz217,

In your scenario, as those three tables have a common filed [employee names], so we can build relationships among those tables based on this common filed.

- employee names and details

- hourly projects, with pay dates, employee names, and project details

- unit projects, with pay dates, employee names, and project details

Regarding the other table "pay dates", if it only contains the column [pay dates], we can't related this table with other three tables accurately, as there is no matched date value from other three tables. But if there is a column [employee names], then we can link it with other tables also use this [employee names] column. I would suggest you take a look at this article: Create and manage relationships in Power BI Desktop.

To create relationships, it depends on how data and columns exist in tables, please share the sample data about those four tables for our analysis.

Best Regards,
Qiuyun Yu

[Anonymous]

Hi @dlz217

Can you please share the data model, data and output desired to figure out a solution.

Cheers

CheenuSIng

[dlz217]

How do I share the model?

[Anonymous]

Hi @dlz217

The data model you can take a screen shot of the data model from power bi and post it here.

And the data you can share it in google drive or dropbox and share the link.

Cheers

CheenuSing