Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Proud to be a Datanaut!
Hi, Pete,
Thanks for your help. It works only in import mode. Still it's very useful.
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
Proud to be a Datanaut!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.