cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kit
Helper I
Helper I

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

Hi,

 

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

PersonDate of Access
Alice1/01/2022
Alice2/01/2022
Alice10/01/2022
Bob4/01/2022
Carol8/01/2022
Dave6/01/2022
Eva4/01/2022
Eva8/01/2022
Eva9/01/2022
Eva10/01/2022
Eva11/01/2022
Eva12/01/2022
Eva22/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!

1 ACCEPTED SOLUTION
ebeery
Super User
Super User

@Kit 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

 

ebeery_0-1646360368211.png

 

View solution in original post

2 REPLIES 2
ebeery
Super User
Super User

@Kit 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

 

ebeery_0-1646360368211.png

 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.