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

[Anonymous]

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!

[rafaelmpsantos · 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])