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

BA_Pete's avatar
BA_Pete
Super User
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 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