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

[Anonymous]

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

[kriscoupe · Accepted solution]

Hi @Anonymous,

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

[Anonymous]

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