Remove Rows Based on Time Columns.

[Anonymous]

Hi All!

I Have a dataset that looks like below. I'm trying to calculate the time duration for each task based on the columns task_start_time and task_end_time. The problem I have here is that the totals are not accurate because there are some tasks that are being performed on parallel by the same person (rows highlighted in red)

Date	Employee Name	Country	Task_Name	Task_Start	Task_End	Minutes
4/19/2021	John Doe	United Kingdom	Design and Test	2:03:00 PM	2:37:00 PM	34
4/19/2021	John Doe	United Kingdom	Design and Test	2:03:00 PM	3:07:00 PM	64
4/19/2021	Paul Smith	Spain	Research Software	2:57:00 PM	3:57:06 PM	60
4/19/2021	Sophia Johnson	Germany	Writing Software	3:10:00 PM	4:30:00 PM	80
4/19/2021	Sophia Johnson	Germany	Testing Software	3:15:00 PM	4:45:00 PM	90
4/19/2021	Noah Brown	Italy	Design and Test	3:59:01 PM	4:28:23 PM	29
4/19/2021	Sophia Johnson	Germany	Manage Operating Systems	5:31:00 PM	6:01:00 PM	29
TOTAL						386

I've tried to solve it with DAX, using the SUMMARIZE Function combined with MIN (task_start) and MAX(task_end), but this is not working ( I think because the task_name is different). This should be the desired output:

Date	Employee Name	Country	Task_Name	Task_Start_Time	Task_End_Time	Minutes_to_Complete
4/19/2021	John Doe	United Kingdom	Design and Test	2:03:00 PM	3:07:00 PM	64
4/19/2021	Paul Smith	Spain	Research Software	2:57:00 PM	3:57:06 PM	60
4/19/2021	Sophia Johnson	Germany	Writing Software / Testing Software	3:10:00 PM	4:45:00 PM	95
4/19/2021	Noah Brown	Italy	Design and Test	3:59:01 PM	4:28:00 PM	29
4/19/2021	Sophia Johnson	Germany	Manage Operating Systems	5:31:00 PM	6:01:00 PM	29
TOTAL						277

¿Ay Idea on how to solve it? I'm stuck on this a couple of days!.

Many Thanks!

[v-yuaj-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your sample .pbix file.

Best Regards,

Yuna

[v-yuaj-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.

Best Regards,

Yuna

[v-yuaj-msft]

Hi @Anonymous ,

Based on your description, you could do some steps as follows. (It is indeed a hard work!)

1. create an index column in Power Query.

2. create some columns

Mark =
VAR a = Test_task[Employee Name]
VAR b =
    MAXX (
        FILTER ( Test_task, [Index] = EARLIER ( Test_task[Index] ) - 1 ),
        [Employee Name]
    )
RETURN
    IF ( a <> b, 1, 0 )

New Category =
VAR x1 =
    RANKX ( FILTER ( ALL ( 'Test_task' ), [Mark] = 1 ), [Index],, ASC )
RETURN
    IF (
        [Mark] = 0,
        MAXX (
            FILTER ( ALL ( 'Test_task' ), [Index] <= EARLIER ( Test_task[Index] ) ),
            x1 - 1
        ),
        x1
    )

3. Create some measures

Combined_Task_Name =
CONCATENATEX ( VALUES ( 'Test_task'[Task_Name] ), [Task_Name], " / " )

Task_Start_Time = MIN('Test_task'[Task_Start])

Task_End_Time = MAX('Test_task'[Task_End])

Minutes_to_Complete =
DATEDIFF ( [Task_Start_Time], [Task_End_Time], MINUTE )

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yuaj-msft 

Sorry for the late answer, but I just checked your idea today.

I think yout solution is close but not all the cases are covered. There are still tasks in parallel. It could be due to the name of the task is blank? You can see it in the screencapture.

[v-yuaj-msft]

Hi @Anonymous ,

Based on the screenshot attached, I think maybe it is due to the "New Category" column or the blank Task_name.

Please do not sum the "New Category" column. I just made one solution based on the data you provided. There is no blank task_name in the sample data.

If you are still confused about it, please provide me with more details about your table and your desired result. I can do tests for you.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

there are three records of Sophia Johnson. Why not combine three records together?

[Anonymous]

Hi @ryan_mayu . I don't want to combine them because at the end of the day, my client wants to see a list of all the different tasks performed by all the Employees.

[ryan_mayu]

@Anonymous 

I think it's very hard to provide the best solution for you. Since I am not sure how complicated your real scenario is.

The most tricky part is 'PARALLEL'.

Do we need to combine the three tasks for below scenario?

Another tricky part is what if two employees have the same name in one country.

I can provide a workaround for your sample data, but I don't think that is a good one.Since we don't know the NOT PARALLEL time is earlier or later.

Hope I didn't make an easy thing to be difficult.

Let's see if any experts can help you.

[Anonymous]

Hi @ryan_mayu . As you said, the more complicated things are the 'Parallel' tasks. We can combine the three rows in one because they are in parallel. In fact, we could ignore the 'Task Name' and have an output like this:

Two employees can't have the same name in different countries because in the real data, we use a unique employee id instead of the name.

The not parallel time can be earlier or later, depending on the task. Unfortunately, we don't have more clues about the data. 😞

I hope it helps a bit to understand the problem.

Thanks.