Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Create column in a table with values extracted from a related table

Dear all,

 Please can you help me with the following:

 

Table 1

Date | Technician name | Worked hours |

 

There are multiple entries for a single date for the same Technician, e.g.

 

1 Jan 2018 | John |  6 hours

1 Jan 2018 | John |  3 hours

1 Jan 2018 | John |  4 hours

 

Table 2

Date | Technician name | Approved hours |

 

For a single date & technician there can be multiple entries with different approved hours. I only want to consider the highest number

 

1 Jan 2018 | John |  6 hours

1 Jan 2018 | John |  8 hours

1 Jan 2018 | John |  10 hours

 

In this case the highest number is 10 hours.

 

I want to create a third table with:

 

 

Table 3

Date          |  Technician name | Approved hours (max) | Worked hours

1 Jan 2018            John                      10 hours                   13 hours

 

 

Thanks a lot!

1 ACCEPTED SOLUTION

Hi,

 

in this case you need 2 another tables. 

'dimension calendar' and 'dimension techician name'

in these dimension tables you will have all the distinct dates and distinct tecnician names. 

After you may to create a relationship between your dimenios and your facts. 


In the new table (as a visual)... you will use 

dimcalendar[Date] | dimTechinician[Name] | max(Table2[AprovedHours]) | sum(Table1[WorkedHours])


View solution in original post

1 REPLY 1

Hi,

 

in this case you need 2 another tables. 

'dimension calendar' and 'dimension techician name'

in these dimension tables you will have all the distinct dates and distinct tecnician names. 

After you may to create a relationship between your dimenios and your facts. 


In the new table (as a visual)... you will use 

dimcalendar[Date] | dimTechinician[Name] | max(Table2[AprovedHours]) | sum(Table1[WorkedHours])


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.