Forum Discussion
How do I delete duplicates based on a condition?
I have 2 columns in my example below, I need to delete all duplicates on the "Name" column. However, I need to delete those duplicates based on the corresponding values on the second column "Title". And these values are TEXT, not NUMBERS, which makes it more tricky.
I want to filter the names based on their most important title, considering Mega Champion > Super Champion > Champion
Basically, if a certain name is simultaneously more than one kind of champion, I want Power BI to only show me that record as the most important title and delete all the other duplicates.
In my example, the correct result should be:
Please, how would I go about doing this on Power BI?
Hi Anonymous ,
Create a column with below code:-
Title_rank = SWITCH(TRUE(), [title] ="Mega Champion",3, [title] ="Super Champion",2, [title] ="Champion",1)Now create a measure with below code:-
Measure = VAR max_title = CALCULATE ( MAX ( 'Table (3)'[Title_rank] ), 'Table (3)'[Name] = MAX ( 'Table (3)'[Name] ) ) RETURN CALCULATE ( MAX ( 'Table (3)'[title] ), 'Table (3)'[Title_rank] = max_title )Output:-
Thanks,
Samarth
2 Replies
Hi Anonymous ,
Create a column with below code:-
Title_rank = SWITCH(TRUE(), [title] ="Mega Champion",3, [title] ="Super Champion",2, [title] ="Champion",1)Now create a measure with below code:-
Measure = VAR max_title = CALCULATE ( MAX ( 'Table (3)'[Title_rank] ), 'Table (3)'[Name] = MAX ( 'Table (3)'[Name] ) ) RETURN CALCULATE ( MAX ( 'Table (3)'[title] ), 'Table (3)'[Title_rank] = max_title )Output:-
Thanks,
Samarth