Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
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!
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
@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
Proud to be a Super User!
User | Count |
---|---|
99 | |
86 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |