Counting duplicates only once in measure(s)

[arb1782]

Hi

I have some data where each row is an individual. The rows of data are all unique but multiple rows may hold information about the same individual. I want to setup a variety of measures such as demorgraphic characteristics where you can toggle including all rows of data or just the unique ones. Can anyone help me with the correct DAX calculation?

I've put some dummy data together. 

ID	First name	Surname	DOB	Gender	Date of record creation
ID1	Abe	Simpson	01/05/1980	Male	01/01/2020
ID2	Abe	Simpson	01/05/1980	Male	02/01/2020
ID3	Marge	Simpson	01/04/1990	Female	01/01/2020
ID4	Marge	Simpson	01/04/1990	Female	02/01/2020
ID5	Homer	Simpson	01/04/1990	Male	03/01/2020
ID6	Bart	Simpson	01/05/1980	Male	03/01/2020
ID7	Abe	Simpson	01/10/1987	Male	03/01/2020

Thanks

arb

[v-juanli-msft · Accepted solution]

Hi @arb1782 

add columns

Column =
RANKX (
    FILTER (
        'Table',
        'Table'[First name] = EARLIER ( 'Table'[First name] )
            && 'Table'[Surname] = EARLIER ( 'Table'[Surname] )
            && 'Table'[DOB] = EARLIER ( 'Table'[DOB] )
            && 'Table'[Gender] = EARLIER ( 'Table'[Gender] )
    ),
    [Date of record creation],
    ,
    ASC,
    DENSE
)


Column 2 = IF([Column]=1,"Unique","Repeat")

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[arb1782]

Thanks Maggie

[Mariusz]

Hi @arb1782 

Sure, so can you give some examples of the expected outcomes based on the sample?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[arb1782]

Sure, something like the column at the end

ID	First name	Surname	DOB	Gender	Date of record creation	Unique or repeat individual?
ID1	Abe	Simpson	01/05/1980	Male	01/01/2020	Unique
ID2	Abe	Simpson	01/05/1980	Male	02/01/2020	Repeat
ID3	Marge	Simpson	01/04/1990	Female	01/01/2020	Unique
ID4	Marge	Simpson	01/04/1990	Female	02/01/2020	Repeat
ID5	Homer	Simpson	01/04/1990	Male	03/01/2020	Unique
ID6	Bart	Simpson	01/05/1980	Male	03/01/2020	Unique
ID7	Abe	Simpson	01/10/1987	Male	03/01/2020	Unique