cancel
Showing results for
Did you mean:
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.

 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!

1 ACCEPTED SOLUTION
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 =
SUMMARIZE ( 'Table', 'Table'[Person] ),
"@Count", CALCULATE ( DISTINCTCOUNT ( 'Table'[Date of Access] ) )
)
VAR _GreaterThan1 =
FILTER ( _CountbyPerson, [@Count] > 1 )
VAR _Result =
COUNTROWS ( _GreaterThan1 )
RETURN
_Result
``````

2 REPLIES 2
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 =
SUMMARIZE ( 'Table', 'Table'[Person] ),
"@Count", CALCULATE ( DISTINCTCOUNT ( 'Table'[Date of Access] ) )
)
VAR _GreaterThan1 =
FILTER ( _CountbyPerson, [@Count] > 1 )
VAR _Result =
COUNTROWS ( _GreaterThan1 )
RETURN
_Result
``````

Helper I

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

Announcements

#### 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.

#### 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!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors