How to Create a DAX Measure for Conditional Distinct Job Opening Counts
Question details
The user needs a DAX measure that counts unique job openings by Profile, Location, and Job Title combinations in Microsoft Power BI or Power Pivot, ignoring repeated workflow stage rows.
- Product
- Microsoft Power BI / Excel Power Pivot
- Device & OS
- not provided
- Scenario
- The user is building a dynamic report from a table where each profile can appear in multiple workflow stages, job titles, and locations, so a simple distinct profile count is not accurate.
- Observed behavior
- Simple COUNTROWS or DISTINCTCOUNT formulas do not produce the conditional distinct count because they either include repeated stage rows or fail to combine the required columns.
Confirm that your Power Pivot or Power BI data model contains a table with Profile, Location, Job Title, and workflow stage columns, and that you have permission to create measures in the model.
Create a DAX measure with SELECTCOLUMNS, DISTINCT, and COUNTROWS
Use this measure when you need to count unique job openings based on Profile, Location, and Job Title combinations while ignoring workflow stage duplicates.
A DISTINCTCOUNT on a single column is not enough when one profile can appear in multiple workflow stages, job titles, or locations. This pattern first builds a one-row-per-combination table with SELECTCOLUMNS, then removes duplicates with DISTINCT, and finally counts the remaining rows.
In Excel Power Pivot, open the Power Pivot window and click in the calculation area; in Power BI, open the report and go to the Modeling tab.
In Power BI, click New Measure on the Modeling tab. In Power Pivot, click an empty cell in the calculation area below the data table.
Type or paste: Job openings := COUNTROWS(DISTINCT(SELECTCOLUMNS(data,"P",data[Profile],"L",data[Location],"J",data[Job Title])))
Commit the measure, set its number format to Whole Number, and then add it to the values area of a PivotTable or visual.
Try WPS Spreadsheets for simpler pivot reporting without DAX
For users who find DAX and Power Pivot difficult to maintain, WPS Office provides a free, lightweight alternative. It opens and saves Excel workbooks, includes familiar spreadsheet and PivotTable tools, and handles common distinct-count reporting with helper columns and formulas instead of DAX.
- 1. Download WPS Office: Download WPS Office Free from the official WPS website and install it on your device.
- 2. Open your Excel workbook: In WPS Spreadsheets, click File > Open and select your existing .xlsx or .xls file.
- 3. Create a helper column for unique combinations: Add a column that joins Profile, Location, and Job Title, for example =A2&B2&C2, to identify unique job openings.
- 4. Build or refresh a PivotTable: Select the data, go to Insert > PivotTable, and set rows, columns, filters, and values for the report.
- 5. Count unique job openings: Use Remove Duplicates or COUNTIF on the helper column to count distinct job opening combinations.
Frequently Asked Questions
Why does DISTINCTCOUNT(Profile) return the wrong job opening count?
Because a profile can have multiple workflow stage rows, so DISTINCTCOUNT(Profile) counts each profile once and misses separate openings with different locations or job titles. Use the unique combination of Profile, Location, and Job Title instead.
Can I use this DAX measure in both Power BI and Power Pivot?
Yes, the SELECTCOLUMNS, DISTINCT, and COUNTROWS pattern works in both Excel Power Pivot and Power BI because they use the same DAX language.
How do I add the measure to my PivotTable or visual?
In Power Pivot, create the measure in the calculation area and then drag it into the values area of the PivotTable. In Power BI, click New Measure on the Modeling tab and then add the measure to a visual.
Will different locations for the same profile be counted as separate openings?
Yes, SELECTCOLUMNS creates rows with Profile-Location-Job Title combinations, so DISTINCT counts each different location or job title combination as a separate job opening.
Does WPS Office support DAX measures?
WPS Spreadsheets supports PivotTables and common formulas, but DAX measures are Microsoft Power Pivot and Power BI specific. In WPS, you can use helper columns and Remove Duplicates or COUNTIF to count unique job opening combinations.
