topic Re: DAX help calculating Repeat Customers in DAX Commands and Tips

DAX help calculating Repeat Customers

nascarfan22 — Thu, 29 May 2025 16:37:42 GMT

Hi,

I’m working in Power BI and trying to create a DAX formula to classify purchasers as either Repeat Customers or Single Customers based on their Customer ID and the dates they made purchases.

For example:

If a customer makes multiple purchases on the same date (e.g., 5/1/2025), they are considered a Single Customer.
If they make purchases on multiple different dates (e.g., 5/1/2025 and 5/2/2025), they are classified as a Repeat Customer.

If I were doing this in Excel, I would use a Pivot Table to distinct count the number of purchase dates per Customer ID, then use a COUNTIF to determine how many customers have only one purchase date (Single Customer) versus more than one (Repeat Customer). This is what I’m trying to replicate using DAX in Power BI.

These are the DAX formulas I've tried:

Single_Customers = COUNTROWS(filter(DISTINCT('Sales'[Customer ID]),CALCULATE(COUNT('Sales'[Sales Date]))=1)) Repeat_Customers = COUNTROWS(filter(DISTINCT('Sales'[Customer ID]),CALCULATE(COUNT('Sales'[Sales Date]))>1))

Single_Customers = COUNTROWS( FILTER( VALUES('Sales'[Customer ID]), CALCULATE(DISTINCTCOUNT('Sales'[Sales Date])) = 1 ) ) Repeat_Customers = COUNTROWS( FILTER( VALUES('Sales'[Customer ID]), CALCULATE(DISTINCTCOUNT('Sales'[Sales Date])) > 1 ) )

Here is some sample data. If I did this in excel with a pivot table and distinct count it, I'd have 5 Single Customers and 1 Repeat Customer.

Customer ID	Sales Date
3808	2/11/2025
1833	5/16/2025
3808	2/11/2025
2879	2/11/2025
1727	5/16/2025
1727	4/18/2025
2036	2/11/2025
1128	4/18/2025

Re: DAX help calculating Repeat Customers

bhanu_gautam — Thu, 29 May 2025 17:15:30 GMT

nascarfan22 Create a calculated column to count the distinct purchase dates for each customer:

DAX
DistinctPurchaseDates =
CALCULATE(
DISTINCTCOUNT('Sales'[Sales Date]),
ALLEXCEPT('Sales', 'Sales'[Customer ID])
)

Create a calculated column to classify each customer as either "Single Customer" or "Repeat Customer":

DAX
CustomerType =
IF(
'Sales'[DistinctPurchaseDates] = 1,
"Single Customer",
"Repeat Customer"
)

To get the count of Single Customers and Repeat Customers, you can create measures:

DAX
Single_Customers =
CALCULATE(
DISTINCTCOUNT('Sales'[Customer ID]),
'Sales'[CustomerType] = "Single Customer"
)

Repeat_Customers =
CALCULATE(
DISTINCTCOUNT('Sales'[Customer ID]),
'Sales'[CustomerType] = "Repeat Customer"
)

Re: DAX help calculating Repeat Customers

nascarfan22 — Thu, 29 May 2025 17:55:05 GMT

Hi, this solution gives me a split of customers that is 90% repeat and 10% single. When I calculate it in excel it's closer to 60% repeat and 40% single. Is there a step that could have been missed?

Re: DAX help calculating Repeat Customers

Irwan — Fri, 30 May 2025 00:40:40 GMT

hello nascarfan22

i am using your sample data above, please try if this match to your need.

1. create a calculated column to check single or repeat.

Check =
var _Count =
COUNTX(
    FILTER(
        'Table',
        'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&
        'Table'[Sales Date]<>EARLIER('Table'[Sales Date])
    ),
    'Table'[Customer ID]
)
Return
IF(
    _Count=1,
    "Repeat",
    "Single"
)

2. create two measures for single and repeat

Single =
CALCULATE(
    DISTINCTCOUNT('Table'[Customer ID]),
    'Table'[Check]="Single"
)

Repeat =
CALCULATE(
    DISTINCTCOUNT('Table'[Customer ID]),
    'Table'[Check]="Repeat"
)

Hope this will help.

Thank you.

Re: DAX help calculating Repeat Customers

V-yubandi-msft — Fri, 30 May 2025 08:40:15 GMT

Hi nascarfan22 ,

Thank you for posting your query in the Microsoft Fabric Community.

Irwan , has provided a detailed response that aligns with your requirements. Could you please review his guidance along with the provided PBIX file.

If you need any further clarification or additional support, feel free to let us know we’re ready to assist.

Best regards,
Yugandhar.

Re: DAX help calculating Repeat Customers

V-yubandi-msft — Mon, 02 Jun 2025 08:41:55 GMT

Hi nascarfan22 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: DAX help calculating Repeat Customers

V-yubandi-msft — Thu, 05 Jun 2025 12:26:37 GMT

Hi nascarfan22 ,
If you’ve gone through the shared responses and reference links, and they helped solve your issue, please mark the most helpful reply as the Accepted Solution. It really helps others in the community who might be facing the same kind of problem.

Thank You.

Re: DAX help calculating Repeat Customers

V-yubandi-msft — Mon, 09 Jun 2025 14:34:51 GMT

Hi nascarfan22 ,

Could you confirm if your issue has been resolved or if you're still facing challenges. If the solution worked for you, please consider marking it as the accepted solution so it can help other community members. If you're still experiencing issues, feel free to share the details.

Thank You.