topic Re: Calculated column with value from another table based on date range and another value in Power Query

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

pgeorgieff1 — Wed, 20 Jul 2022 09:34:37 GMT

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?

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

BA_Pete — Wed, 20 Jul 2022 15:57:55 GMT

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

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

pgeorgieff1 — Sat, 23 Jul 2022 14:30:44 GMT

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

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

pgeorgieff1 — Sun, 24 Jul 2022 10:01:22 GMT

Hi, Pete,

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