I have two fact tables containing times that i would like to align. One query represents the tasks completed and the other query represents the employee hours and task assigned.
Right now I have the duration of time for the Picking Task query calculated via a dax measure, but I am looking to remove break times based on the Role Log query data. The time duration needs to be down to the second.
An example to reference is cluster ID 16597 (below.) This should have a 15 minute break in the middle of the time, so roughly 15 minutes should be removed based on the role log data.
There is no matching criteria to map the two queries together, so I created one to try and match them. Is my set up correct? Is what I am trying to do possible?
Thanks for posting your question @Jackie. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
I think this should have the details needed. If you need more, please let me know.
The end result we are looking for is to have the time duration of the first pick to last pick minus the break times if there is a captured break inside the Role Log associated with the same employee.
if I understood it correctly, the main point would be to assign the break time to each task record? For instance, for costing purposes?
This could be done if you create a ‘Break time’ column in the ‘Picking task’ table.
Break TIME =
VAR CompleteVar =‘Picking Tasks’[Completed Date]
VAR ClusterVar = ‘Picking Tasks’[Cluster Build Time]
RETURN MAXX (
FILTER (
‘Role Log’,
AND (
‘Role Log’[StartTime] > = ClusterVar,
AND (
‘Role Log’[EndTime] < = CompleteVar,
‘Role Log’[Role] = “BREAK”
)
)
),
‘Role Log’[Hours]
)
Then you can infer the total working time subtracting the break time
If this helped you to solve the problem, I appreciate if you can mark the problem as solved
To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Group. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!
