Calculated column with value from another table based on date range and another value

[pgeorgieff1]

Hi, 

I have two tables in DirectQuery mode: Tasks and Work Hours. 

   

The table Tasks shows how much an employee spent on task on particular day.

Work Hours shows how much a person is available between two dates. 

I want to add a column Working hours from Table Work Hours to Table Tasks based on Employee ID and Completed Date. I also want to add another column Total hrs / day, which calculates how many hours an Employee spent in a day again based on Employee ID and Completed Date. 

In excel I achieved this by the following formulas:

For working hrs: =INDEX(M4:M9;MATCH(1;(E4=L4:L9)*(C4>=N4:N9)*(C4<=O4:O9);0))

For Total hrs / day: SUMIFS(F4:F11;C4:C11;C4;E4:E11;E4)

Can you please help me add those columns in the table Tasks?

[pgeorgieff1]

Hi, Pete, 

Thanks for your help. It works only in import mode. Still it's very useful. 

[BA_Pete · Accepted solution]

Hi @pgeorgieff1 ,

Try this as a calculated column on your Tasks table, but I'm not sure everything here is compatible with Direct Query, to be honest:

..workingHours =
CALCULATE(
	VAR __empIdRow = VALUES(TasksTable[Employee ID])
	VAR __compDateRow = VALUES(TasksTable[Completed Date])
	RETURN
	MAXX(
		FILTER(
			workHoursTable,
			workHoursTable[Employee ID] = __empIdRow
			&& workHoursTable[Start date] <= __compDateRow
			&& workHoursTable[End date] >= __compDateRow
		),
		workHoursTable[Working hrs]
	)
)

Pete

[pgeorgieff1]

Hi Pete, 

Thanks your reply. 

I did lots of transformation which delayed me with the reply. 

There are no error in the rows, but all of them are blank. Have you got an idea what the reason can be? 

Regards,

Philip