Forum Discussion

pgeorgieff1's avatar
pgeorgieff1
Regular Visitor
4 years ago
Solved

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

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...
Need Help
Tutorial Requests
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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's avatar
pgeorgieff1
Regular Visitor
4 years ago

Hi, Pete, 

 

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