How to Create a DAX Measure to Count Customers with Exactly Ten Purchases
Question details
The user needs to construct a DAX measure that accurately counts the number of customers who have made exactly ten distinct purchases, ensuring that the count dynamically respects the current reporting period and filter context.
- Product
- Excel Power Pivot / Power BI
- Device & OS
- not provided
- Scenario
- Analyzing customer behavior and frequency by filtering and counting distinct users based on exact purchase volumes.
- Observed behavior
- The goal is to return a precise integer count of specific customers matching the criteria within a given date range or filter context.
Ensure your data model features active relationships between your sales and customer tables, and confirm that the column used for purchases (e.g., Sales Document) uniquely identifies each transaction.
Using COUNTROWS, FILTER, and CALCULATE
This is the most reliable DAX pattern to isolate and count customers with a specific number of transactions.
This formula works by generating a unique list of customers in the current filter context, calculating the distinct count of their purchases, and then retaining only those whose total exactly equals 10.
Navigate to your Power Pivot window in Excel or the Data pane in Power BI and click to create a 'New Measure'.
Enter a descriptive name for your measure, for example: CustomersWithExactly10Purchases =
Type the following formula: COUNTROWS(FILTER(VALUES(Sales[Sold-to party]), CALCULATE(DISTINCTCOUNT(Sales[Sales Document])) = 10))
Replace 'Sales[Sold-to party]' with your actual customer ID column, and 'Sales[Sales Document]' with your unique invoice or order ID column.
Press Enter to save the measure, then drag and drop it into your PivotTable or visual report to see the dynamic count.
Troubleshooting Incorrect Measure Results
Use this checklist if your DAX measure returns blanks or incorrect customer counts.
Need advanced data analysis tools? Try WPS Office for free.
While DAX modeling is native to Microsoft's Power Pivot environment, WPS Office offers a comprehensive, free, and lightweight alternative for everyday and advanced spreadsheet tasks. Experience robust formula execution, PivotTables, and seamless file compatibility.
- 1. Download the App: Visit the official WPS Office website to download the free suite for your device.
- 2. Install and Launch: Run the installer and open WPS Spreadsheet.
- 3. Open Your Data File: Open your existing Excel workbooks seamlessly to continue analyzing your data using PivotTables and functions.
Frequently Asked Questions
Can I change the number of purchases to a dynamic value instead of exactly 10?
Yes. You can replace the hardcoded '10' in the DAX formula with a reference to another measure or a What-If parameter (e.g., [Selected Purchase Threshold]), allowing users to select the threshold via a slicer.
Why does my measure return a blank instead of zero?
In DAX, if no rows meet the criteria within the FILTER function, COUNTROWS returns a blank. If you prefer to display a zero, you can append '+ 0' to the end of your measure.
Should I use COUNT or DISTINCTCOUNT inside the CALCULATE function?
Use DISTINCTCOUNT if a single purchase might appear across multiple rows in your table (such as individual line items in a single invoice). If every row in your sales table represents a completely unique purchase, COUNT or COUNTROWS is sufficient and more performant.
