DAX: Count the number of values that appear more than once in a column

[Anonymous]

Hi,

Suppose I have a list of people, along with the dates that they accessed a venue, as below.

Person	Date of Access
Alice	1/01/2022
Alice	2/01/2022
Alice	10/01/2022
Bob	4/01/2022
Carol	8/01/2022
Dave	6/01/2022
Eva	4/01/2022
Eva	8/01/2022
Eva	9/01/2022
Eva	10/01/2022
Eva	11/01/2022
Eva	12/01/2022
Eva	22/01/2022

I'd like to count the number of people who accessed the venue more than once. Based on the data above, the answer should be 2 (Alice and Eva). 

How do you make this calculation using DAX?

Thanks!

[ebeery · Accepted solution]

@Anonymous there are probably more consise ways to accomplish, but I find it helpful to do such calculations in steps so that so that I can better visualize what's happening.

Here's one way:

Count Duplicates = 
VAR _CountbyPerson =
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Person] ),
        "@Count", CALCULATE ( DISTINCTCOUNT ( 'Table'[Date of Access] ) )
    )
VAR _GreaterThan1 =
    FILTER ( _CountbyPerson, [@Count] > 1 )
VAR _Result =
    COUNTROWS ( _GreaterThan1 )
RETURN
    _Result

[suma2413]

Hello,

Found this solution useful fro one of my task in 2024 here.. I wanted to add a small condition to the above question where the Date of Access should be less than of equal to today. Could you please help?

[Anonymous]

Thank you very much, @ebeery. The way you laid it out makes it easy for novices like me to follow your logic too!