Forum Discussion
Resolver IIILookup value based on multiple conditions from another table
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 |
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:
3 Replies
StrategicSavvyResolver II
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]) ) )
- DataNinja777
Super User
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:
