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
rachaelwalker
Resolver III
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 NumberDateHours Reported
11873/20/2024       16
60773/28/2024        8
11112/29/2024        4
60163/1/2024         3

 

Time Entries

Job NumberDate EnteredTechnicianHours Worked
60773/30/2024Bobby         8
60773/29/2024Bobby         9
60773/28/2024Bobby         8
60773/27/2024Bobby         8
60163/1/2024Larry         3
60162/22/2024Larry        4.5
60162/16/2024Larry         5
60162/7/2024Sam       4.5
60778/4/2016Brad       1.5
60776/3/2016Brad       1.5

 

Expected Results

Job NumberDateHours ReportedTechnicianHours Worked
11873/20/202416  
60773/28/20248Bobby      8
11112/29/20244  
60163/1/20243Larry       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 NumberDateHours ReportedTechnicianHours Worked
60773/28/20248Bobby (8), Sam (2)        10



1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

Hi @rachaelwalker 

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

DataNinja777_0-1712462098419.png

The above formula results in the output like below:

DataNinja777_1-1712462121164.png

View solution in original post

3 REPLIES 3
DataNinja777
Super User
Super User

Hi @rachaelwalker 

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

DataNinja777_0-1712462098419.png

The above formula results in the output like below:

DataNinja777_1-1712462121164.png

StrategicSavvy
Resolver II
Resolver II

Hi @rachaelwalker 

 

How about using multiple search conditions using LOOKUP formula : 

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

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

 

rachaelwalker_0-1712263321817.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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