Table with duplicate rows by name but different status.

[SMatos94]

Hello,

Table with duplicate rows by name but different status.

I have a table with duplicate rows by user name, but with different status. I need to clear the table, so that in case of duplicate rows, only the activation persists.

Does anybody know how to solve this?

In this example, you would need to analyze lines one and two, keeping only the one with active status.
The same for lines 4 and 5.

Name	Status	Situation	ID
Ana Silva	Active	Normal activity	1234
Ana Silva	31/05/2024	Transfer	1234
Joao Souza	Active	Normal activity	2345
Maria Paula	Active	Normal activity	3456
Maria Paula	07/04/2024	Transfer	3456
Mario Oliveira	28/02/2023	Company termination	6789
Edson Vieira	09/07/2022	Company termination	7894

[v-yohua-msft]

Hi, @SMatos94 

Create a new table that contains only the rows in which Activity is created. This will effectively remove duplicate rows that do not have a status of Active. Use the following DAX:

NewTable = 
CALCULATETABLE(
    'YourTable',
    'YourTable'[Status] = "Active"
)

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[SMatos94]

The problem is that I need to know if the employee was terminated. So I can't remove the rows with status other than "Active".

[v-yohua-msft · Accepted solution]

Hi, @SMatos94 

I made some modifications. I create a new table and use the following DAX:

FilteredTable = FILTER(
    ALL('Table'),
    [Situation] = "Normal activity" || [Situation] = "Company termination"
)

Here is the preview:

 

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[GilbertQ]

Hi @SMatos94 

Equal to the status column to where it equals active.?

[SMatos94]

Yes!