cancel
Showing results 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

Resolver III

## Lookup 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

1 ACCEPTED SOLUTION
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:

3 REPLIES 3
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:

Resolver II

How about using multiple search conditions using LOOKUP formula :

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

Resolver III

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])
)
)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors