Forum Discussion
Removing duplicate rows based on three different columns
Hi Power BI community
I have a table called WorkPlanEntry with all the workday that are planned for a company.
My problem is that whenever a plan is edits the data added as a row in the table, but the old row is not deleted.
The table has three Columns
[User_id] (a number that indicate which user we are looking at.)
[Date] (is the date where the holiday is planned.)
[Unique_id] (a number that gets higher the later the the plan is added to the system.)
If a day is planned twice for the same [User_id] we should only look at the plan with the higehst [Unique_id] since its an edit.
My Dax right now look like this
Measure = COUNTROWS(RELATEDTABLE('WorkPlanEntry')
It adds all the rows togther but gets to many rows when I have the table has been edited.
Can anyone help me remove the extra rows?
All help that gets me closer to a solution is greatly appreciated
ThomasWeppler , You can try creating new calculated table
LatestWorkPlanEntry =
SUMMARIZE(
'WorkPlanEntry',
'WorkPlanEntry'[User_id],
'WorkPlanEntry'[Date],
"LatestUniqueID", MAX('WorkPlanEntry'[Unique_id])
)This table will contain only the latest entries for each User_id and Date.
3 Replies
ThomasWeppler , You can try creating new calculated table
LatestWorkPlanEntry =
SUMMARIZE(
'WorkPlanEntry',
'WorkPlanEntry'[User_id],
'WorkPlanEntry'[Date],
"LatestUniqueID", MAX('WorkPlanEntry'[Unique_id])
)This table will contain only the latest entries for each User_id and Date.
It worked like a charm.
Thanks bhanu_gautam for the quick answer.
From your description, it seems ([User_id], [Date]) can be used as "composite key"; thus
= COUNTROWS( SUMMARIZECOLUMNS( WorkPlanEntry[User_id], WorkPlanEntry[Date] ) )
