topic DAX - Identify Duplicate Column values in Developer

DAX - Identify Duplicate Column values

BIbeginner — Wed, 27 Dec 2017 00:31:11 GMT

Hi All

How to identify the duplicate values and mark as sequential number (1,2,3...) using Rankx or Row Number Partition by functions.

Thanks

Re: DAX - Identify Duplicate Column values

prakash_gautam — Wed, 27 Dec 2017 03:12:52 GMT

Not sure using Rankx or Row Number but below DAX works well if there is
unique field/id:

dup =
Var pid = Products[UNID]
return CALCULATE (
COUNTROWS ( Products ),
ALLEXCEPT ( Products, Products[EFFECTIVEDATE] ),
pid >= Products[UNID]
)

ALLEXCEPT ( Products, Products[EFFECTIVEDATE] ) is the field you want to find the duplicate by and
you can include multiple fileds here and Products[UNID] is unique filed/row identifier of the products table.

Thanks

Re: DAX - Identify Duplicate Column values

v-chuncz-msft — Wed, 27 Dec 2017 10:02:42 GMT

@BIbeginner,

Check replies in links below that use Power Query and DAX.

https://community.powerbi.com/t5/Desktop/Remove-Rows-in-Dataset-based-on-most-recent-ID-and-email-address/td-p/225194
https://community.powerbi.com/t5/Desktop/how-can-I-show-top-3-data-with-a-combined-categories/td-p/192108