cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shailu198 Regular Visitor
Regular Visitor

Creating new calculated field using multiple columns from multiple tables

Hi All,

 

I have a scenario where i want to create a calculated column which is using two other columns from 2 other tables.

I have 3 tables as  - labor_terminations, employee & workday_cost_centers. These three tables are connected with each other.

labor_terminations is connected with workday_cost_centers and employee is also connected with workday_cost_centers.

Please find the screenshots for understanding the relation.

 

When i am trying to create new column in labor_terminations i want one column from employee and one from workday_cost_centers. Since labor_terminations is not directly connected with employee it is not showing any column of employee table even with the use of related keyword.

 

Capture.JPG

 

Any help would be greatful.

 

Regards,

Shailesh

 

1 REPLY 1
Moderator Eric_Zhang
Moderator

Re: Creating new calculated field using multiple columns from multiple tables


@Shailu198 wrote:

Hi All,

 

I have a scenario where i want to create a calculated column which is using two other columns from 2 other tables.

I have 3 tables as  - labor_terminations, employee & workday_cost_centers. These three tables are connected with each other.

labor_terminations is connected with workday_cost_centers and employee is also connected with workday_cost_centers.

Please find the screenshots for understanding the relation.

 

When i am trying to create new column in labor_terminations i want one column from employee and one from workday_cost_centers. Since labor_terminations is not directly connected with employee it is not showing any column of employee table even with the use of related keyword.

 

Capture.JPG

 

Any help would be greatful.

 

Regards,

Shailesh

 


@Shailu198

The snapshot shows the table labor and employ are in a many-to-many relationship, so you'll have to use RELATEDTABLE. So to create a calculated column in the table labor, the DAX shall be like

calculated column = SUMX(RELATEDTABLE(employee),employee[somecolumn])

For further advice, please post some smaple data nd expected output.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors