Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Match 2 columns and lookup value

Hi,

 

I've 2 tables, 1st with list of Projects currently tested (Multiple projects & Multiple teams)  

2nd with hours each resource spent from those teams on the projects.

 

I want to calculate the actuals per Project & Team combination by looking up 2nd table

 

Current Project List  

 

Project_NumberProject_nameTeamStatusApplicationRel_Start_dateEstimate

PR1PName1Team1In ProgressApp114/3/2020150
PR2PName2Team1In ProgressApp114/3/2020150
PR5PName5Team1HoldApp124/3/2020100
PR1PName1Team2In ProgressApp214/3/2020175
PR2PName2Team2In ProgressApp224/3/2020100
PR3PName3Team2In ProgressApp214/3/2020150
PR5PName5Team2In ProgressApp234/3/202075
PR6PName6Team2HoldApp224/3/2020150
PR2PName2Team3In ProgressApp314/3/2020100
PR3PName3Team3In ProgressApp324/3/2020150
PR5PName5Team4In ProgressApp414/3/2020300
PR3PName3Team5In ProgressApp514/3/2020150
PR6PName6Team5In ProgressApp514/3/2020145
PR4PName4Team6In ProgressApp614/3/2020170
PR1PName1Team6In ProgressApp614/3/2020180
PR6PName6Team6In ProgressApp614/3/2020190
PR4PName4Team6In ProgressApp624/3/2020140
PR3PName3Team7HoldApp714/3/2020160
PR2PName2Team7In ProgressApp714/3/2020250
PR1PName1Team8In ProgressApp814/3/2020120
PR5PName5Team8In ProgressApp814/3/202080

 

Table : 2

PR_NumberTeamResourceBooking_dateHours

PR1Team1Res14/1/20208
PR2Team1Res14/2/20208
PR1Team1Res14/3/20208
PR2Team1Res14/6/20208
PR1Team1Res24/1/20208
PR2Team1Res24/2/20208
PR1Team1Res24/3/20208
PR2Team1Res24/6/20208
PR6Team2Res214/1/20208
PR1Team2Res214/2/20208
PR2Team2Res214/3/20208
PR3Team2Res214/6/20208
PR1Team2Res224/1/20208
PR4Team2Res224/2/20208
PR1Team2Res224/3/20208
PR2Team2Res224/6/20208
PR2Team3Res314/1/20208
PR2Team3Res314/2/20208
PR2Team3Res314/3/20208
PR2Team3Res314/6/20208
PR3Team3Res324/1/20208
PR3Team3Res324/2/20208
PR3Team3Res324/3/20208
PR3Team3Res324/6/20208
PR5Team4Res414/1/20208
PR5Team4Res414/2/20208
PR5Team4Res414/3/20208
PR5Team4Res414/6/20208
PR5Team4Res424/1/20208
PR5Team4Res424/2/20208
PR5Team4Res424/3/20208
PR5Team4Res424/6/20208
PR3Team5Res514/1/20208

 

 

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hello @Anonymous ,

 

You may try this:

 

Estimate Hours = SUM(table1[Estimate])

Actual Hours = SUM(table2[Hours])

Total Hours Team = 
CALCULATE(
    [Actual Hours],
    TREATAS(VALUES(table1[Team]),table2[Team]),
    TREATAS(VALUES(table1[Project_Number]),table2[PR_Number])
)

 

Output:

Capture.JPGSolution pbix file here

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

View solution in original post

2 REPLIES 2
vivran22
Community Champion
Community Champion

Hello @Anonymous ,

 

Did it solve your issue? If yes, then please mark it as a solution as it will help others in the community.

 

If not, then please share more details.

 

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

vivran22
Community Champion
Community Champion

Hello @Anonymous ,

 

You may try this:

 

Estimate Hours = SUM(table1[Estimate])

Actual Hours = SUM(table2[Hours])

Total Hours Team = 
CALCULATE(
    [Actual Hours],
    TREATAS(VALUES(table1[Team]),table2[Team]),
    TREATAS(VALUES(table1[Project_Number]),table2[PR_Number])
)

 

Output:

Capture.JPGSolution pbix file here

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors