Distinct Count of Individuals using Date of Birth as a criteria

[jharris32]

Hi!
I need a distinct count of individuals in a dataset, but would like to use the individuals date of birth as criteria since there will be people in our list with the same name but a different date of birth. So essentially a distinct count using the individuals name AND date of birth to determine whether it is truly a unique individual. Any suggestions?

[v-heq-msft · Accepted solution]

Hi @jharris32 ,
@bhanu_gautam good reply! 
Here is another way to achieve this. 

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Open power query and right click the table and choose Duplicate, in table(2), group by columns

2.Close and apply, create a meure

DISTINCT COUNT BY NAME AND BIRTH = COUNT('Table (2)'[Count])

3.Final output

Best regards,

Albert He

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

[jharris32]

Hi @v-heq-msft , thank you - this is helpful!! If I think wanted to further narrow things down by another variable (so first getting a distinct count based on age/dob, then taking that first distinct list and narrowing down with street address), would that be possible? Or can I only use one metric?

Thanks!

[v-heq-msft]

Hi @jharris32 ,
Thank you for your reply, if you want to limit the scope of the search by another indicator, you can do so by adding another column in the first step. Of course, if you want to go for limiting in the altered table (i.e. Table(2) above), you can also do it via distinctcount.

Best regards,

Albert He

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

[bhanu_gautam]

@jharris32 , You can create one calculated column concatenating DOB and name using below method

UniqueIdentifier = CONCATENATE([Name], "-", FORMAT([DateOfBirth], "YYYY-MM-DD"))

And from this column you can create a measure for Distinct value

UniqueIndividualsCount = DISTINCTCOUNT('YourTableName'[UniqueIdentifier])

Please accept as solution and give kudos if it helps