cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Need to calculate distinct count of customers only if the count is greater then one

Hello Power BI community!

I have what looks to be a simple task. I have a table with restaurant reservations. I need to find the repeating customers. In other words I need to do counts of customers where count > 1. This is what I wrote:

Distinct Customers = CALCULATE (
DISTINCTCOUNT ( Resy[Contact_Name__c] ),
FILTER(Resy, DISTINCTCOUNT(Resy[Contact_Name__c] ) > 1)
)

Contact_Name__c is a unique customer identifier. If I drop that measure in a matrix my numbers are way to high.

If I do the same calculation in SQL Server I get:

T-SQL

SELECT DISTINCT COUNT(ContactName), Venue
FROM
(
SELECT COUNT(Contact_Name__c) CNT, Contact_Name__c AS ContactName, Venue_Name__c AS Venue
FROM [AnalyticsBI].[sf].[Resy]
GROUP BY Contact_Name__c, Venue_Name__c
HAVING COUNT(Contact_Name__c) > 1
) sql1
GROUP BY Venue

Does anybody have any idea why the counts are off?

Thanks!

Stan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver IV

## Re: Need to calculate distinct count of customers only if the count is greater then one

Hi @zaichusha,

Might be able to help on this one. The reason your numbers are too high is likely because your haven't initiated context transition in your FILTER statement. FILTER iterates row-by-row and will be putting the value of the DISTINCTCOUNT of customers in every row (this is always going to be > 1 unless you have 1 or zero customers). Try this

``````Distinct Customers =
COUNTROWS(
FILTER(
VALUES( Resy[Contact_Name__c] ),
CALCULATE( COUNTROWS( Resy ) ) > 1
)
)``````

Effectively this formula create a table with the unique list of Contact_Name_c (the VALUES) and iterates over this keeping the Contact_Name_c where there is more than 1 row in the table. The COUNTROWS then counts these up.

Hope it helps.

Kris

2 REPLIES 2
Highlighted
Resolver IV

## Re: Need to calculate distinct count of customers only if the count is greater then one

Hi @zaichusha,

Might be able to help on this one. The reason your numbers are too high is likely because your haven't initiated context transition in your FILTER statement. FILTER iterates row-by-row and will be putting the value of the DISTINCTCOUNT of customers in every row (this is always going to be > 1 unless you have 1 or zero customers). Try this

``````Distinct Customers =
COUNTROWS(
FILTER(
VALUES( Resy[Contact_Name__c] ),
CALCULATE( COUNTROWS( Resy ) ) > 1
)
)``````

Effectively this formula create a table with the unique list of Contact_Name_c (the VALUES) and iterates over this keeping the Contact_Name_c where there is more than 1 row in the table. The COUNTROWS then counts these up.

Hope it helps.

Kris

Highlighted
Helper II

## Re: Need to calculate distinct count of customers only if the count is greater then one

Kris, that did the trick! Thank you! I will still have to grasp the DAX.

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors