How to Create a DAX Measure in Excel Power Pivot to Count Names with Multiple Certifications
Question details
The user needs an efficient DAX measure that counts names only when they have more than one distinct certification and wants a meaningful total row.
- Product
- Microsoft Excel Power Pivot (Data Model)
- Device & OS
- not provided
- Scenario
- The user is analyzing a certification table that has Name and Certification columns, such as a training or compliance register.
- Observed behavior
- A standard distinct count or simple filter can count the same name multiple times or show an incorrect grand total.
Load the certification table into the Excel Data Model (Power Pivot), name the table Table1, and confirm the Name and Certification columns do not contain blanks in the rows to be counted. Ensure the Power Pivot add-in is enabled in Excel before creating measures.
Create the Base DAX Measure for Names with More Than One Distinct Certification
Use this measure first when you need the row-level count of names that have more than one distinct certification in the current filter context.
The base measure works by iterating over the distinct Name values, then using CALCULATE and ALL(Table1[Certification]) to ignore the certification filter and count how many distinct rows or certifications belong to each name. Only names with a count greater than 1 are kept.
For pivot table totals, pair this measure with the companion total measure in the next solution.
In Microsoft Excel, go to the Data tab and click Manage Data Model to open the Power Pivot window.
In the Power Pivot window, click the table that contains the Name and Certification columns, for example Table1.
On the Home or Design tab, click New Measure, enter Available as the measure name, and paste the following DAX formula: Available := COUNTROWS(FILTER(VALUES(Table1[Name]), CALCULATE(COUNTROWS(DISTINCT(Table1)), ALL(Table1[Certification])) > 1))
Click OK to save the measure, then create a PivotTable from the Data Model and add Available to the Values area.
Confirm that each visible row shows the count of names that satisfy the multiple-certification condition for the current filter context.
Add the Total Available Measure for a Meaningful PivotTable Total
Use this companion measure in the Values area so the grand total sums the visible row-level counts instead of showing a single filtered value.
Need a Free Alternative for DAX-Specific Excel Tasks? Try WPS Office
Because DAX measures for counting multiple certifications are created in Microsoft Excel's Power Pivot Data Model, WPS Office does not provide a DAX editor for this task. WPS Office is a free, lightweight Office alternative that opens .xlsx files, preserves Excel-compatible formatting and formulas where supported, and includes familiar spreadsheet and pivot table tools for daily analysis.
- 1. Download WPS Office: Go to the official WPS Office download page and download the free installer for Windows, macOS, iOS, or Android.
- 2. Install and open WPS Spreadsheets: Install WPS Office, then open WPS Spreadsheets from the WPS Office home screen.
- 3. Open your Excel workbook: Use File > Open to open the .xlsx workbook containing your certification data and view or edit it.
- 4. Use built-in spreadsheet tools: Use WPS Spreadsheets for compatible formulas, pivot tables, charts, and sharing; for DAX-specific Power Pivot measures, use Microsoft Excel.
Frequently Asked Questions
Can I create DAX measures in WPS Office Spreadsheets?
No. DAX measures such as COUNTROWS, FILTER, CALCULATE, HASONEVALUE and SUMX are created and edited in the Microsoft Excel Power Pivot Data Model. WPS Office can open the workbook, but it does not provide a DAX editor to create or modify Power Pivot measures.
Why does the base measure show a different total than the sum of the visible rows?
The base Available measure is evaluated in the current certification filter context. Use the Total Available measure with HASONEVALUE and SUMX over VALUES(Table1[Certification]) so the grand total sums the visible row-level values.
Where do I enter the DAX formula in Excel?
In Excel, enable the Power Pivot add-in if needed, then go to Data > Manage Data Model. In the Power Pivot window, select the table, click New Measure on the Home or Design tab, and paste the formula into the formula box.
Can I count names with multiple distinct certifications without using DAX?
Yes. You can add a helper column that concatenates Name and Certification, then use COUNTIF or COUNTIFS on that key to flag names that occur with multiple certification values, or use PivotTable filters. A DAX measure is more flexible when the data is refreshed or filtered.
