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 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?

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

3 Replies

  • 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

  • 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.