Remove Duplicates based on Condition

[StuartSmith]

I have a table similar to the below...

VR Account		Building Code		City		Country		Region
VR1		B1		Blah
VR2		B2
VR2
VR2
VR3		B2
VR3		B2
VR4

I want to remove duplicate "VR Account" values, but if there are duplicate "VR Account" vales that also has a "Building Code" value that take priority over a "VR Account" with no "Building Code".  Hope that makes sense & thanks in advance.

[saud968 · Accepted solution]

Create a Rank Column:

Use the RANKX function to assign a rank to each row based on your priority rule.
You can rank by "VR Account" and then within each "VR Account" rank by the presence of a "Building Code".
Rank = RANKX(ALL('YourTable'), 'YourTable'[VR Account], ASC, 'YourTable'[Building Code], DESC)
Filter for Unique Rows:

Create a new calculated table to filter for the top-ranked row for each "VR Account".
UniqueTable = FILTER(
ALL('YourTable'),
'YourTable'[Rank] = 1
)
Visualize the Result:

Use the UniqueTable in your visualizations to display the desired output.
Example DAX:

Rank = RANKX(
ALL('YourTable'),
'YourTable'[VR Account],
ASC,
IF(ISBLANK('YourTable'[Building Code]), 1, 0),
DESC
)

UniqueTable = FILTER(
ALL('YourTable'),
'YourTable'[Rank] = 1
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Anonymous · Accepted solution]

Hi @StuartSmith ,

Based on the description, the method @ provided should be helpful.

Besides, try to open power query editor.

Then, add a custom column.

Select the data by custom column in descending.

Remove duplicates by selecting the VR Account column and using the Remove Duplicates option.

The desired result is shown below.

Best Regards,

Wisdom Wu

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

[saud968 · Accepted solution]

Create a Rank Column:

Use the RANKX function to assign a rank to each row based on your priority rule.
You can rank by "VR Account" and then within each "VR Account" rank by the presence of a "Building Code".
Rank = RANKX(ALL('YourTable'), 'YourTable'[VR Account], ASC, 'YourTable'[Building Code], DESC)
Filter for Unique Rows:

Create a new calculated table to filter for the top-ranked row for each "VR Account".
UniqueTable = FILTER(
ALL('YourTable'),
'YourTable'[Rank] = 1
)
Visualize the Result:

Use the UniqueTable in your visualizations to display the desired output.
Example DAX:

Rank = RANKX(
ALL('YourTable'),
'YourTable'[VR Account],
ASC,
IF(ISBLANK('YourTable'[Building Code]), 1, 0),
DESC
)

UniqueTable = FILTER(
ALL('YourTable'),
'YourTable'[Rank] = 1
)

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[StuartSmith]

Thanks, I will try later and let you know the outcome.