Lookup value based on multiple conditions from another table

[rachaelwalker]

I have two tables that are related many-to-many. They are related by Job Number. Field Rework and Time Entries. From the Field Rework table, I would like to lookup the sum of hours in the Time Entries table based on when the dates are the same and who the technician was

Field Rework

Job Number	Date	Hours Reported
1187	3/20/2024	16
6077	3/28/2024	8
1111	2/29/2024	4
6016	3/1/2024	3

Time Entries

Job Number	Date Entered	Technician	Hours Worked
6077	3/30/2024	Bobby	8
6077	3/29/2024	Bobby	9
6077	3/28/2024	Bobby	8
6077	3/27/2024	Bobby	8
6016	3/1/2024	Larry	3
6016	2/22/2024	Larry	4.5
6016	2/16/2024	Larry	5
6016	2/7/2024	Sam	4.5
6077	8/4/2016	Brad	1.5
6077	6/3/2016	Brad	1.5

Expected Results

Job Number	Date	Hours Reported	Technician	Hours Worked
1187	3/20/2024	16
6077	3/28/2024	8	Bobby	8
1111	2/29/2024	4
6016	3/1/2024	3	Larry	3

In some cases there are multiple technicians so there will be multiple entries by different techs, so I have a measure to list them in a string using CONCATENATEX which is working great; however is there a way to include the hours completed by each in parentheses? "Bobby (8), Sam (2)"

Technician List = CONCATENATEX(VALUES('Time Entries'[Technician]),[Technician],", ")

Example: 

Job Number	Date	Hours Reported	Technician	Hours Worked
6077	3/28/2024	8	Bobby (8), Sam (2)	10

[DataNinja777 · Accepted solution]

Hi @rachaelwalker 

Regarding the ConcatenateX usage, you can flexibly get the result you want by writing a formula like below:

The above formula results in the output like below:

[StrategicSavvy]

Hi @rachaelwalker 

How about using multiple search conditions using LOOKUP formula : 

LOOKUPVALUE (
<result_columnName>,
<search_columnName>,
<search_value>
[, <search2_columnName>, <search2_value>]…
[, <alternateResult>]
)

[rachaelwalker]

I cannot get lookupvalue to work. It says it cannot see my column in the Time Entries table. I used a similar measure but it is returning the sum of all hours and not the sum for just that date. I understand what it is doing and why I am getting the results but I dont know what to change to make it work the way I need it to

Hours Worked Look UP = 
CALCULATE(
    SUM('Time Entries'[Hours Worked]),
    FILTER(
        ALLNOBLANKROW('Field Rework and Delay Tracker'[Date]),
        'Field Rework and Delay Tracker'[Date] = MAX('Time Entries'[Date Entered]) 
    )
)