digest
Short Summary
Main recommendation: For counting unique items in a large list, use the DAX function DISTINCTCOUNT (in Power Pivot or a PivotTable's Value Field Settings) rather than array formulas or a SUMPRODUCT/COUNTIF approach, which the video says becomes very slow on large data. The video demonstrates this on a list of almost 20,000 names, which returns 72 unique names. It also notes that for complex relational lookups without helper columns, importing tables into Power Pivot and building relationships is far easier than a complicated array formula.
Key Points
- The video compares a complicated array formula, a SUMPRODUCT/COUNTIF formula (described as fine for small data sets but very slow on ~20,000 rows), and DISTINCTCOUNT, which is far simpler and faster.
- DISTINCTCOUNT can be used directly in a regular PivotTable by changing the summary function from Count/Sum to DISTINCTCOUNT, with no need to open Power Pivot.
- Creating an explicit calculated field (measure) in Power Pivot lets you format it and reuse it in other formulas, which the speaker says matters for later calculations such as average sales per day.
- For lookups across tables without helper columns, array formulas are described as extremely complicated, while Power Pivot relationships are presented as the easier alternative.
Details Worth Noting
- Concrete instructions:
- Convert the data range to a table (Control T, then Enter), and name it (Alt JTA in the video).
- Add the table to the data model via PivotTable > Insert PivotTable (Alt NV), checking "Add this data to the Data Model."
- To use DISTINCTCOUNT in a PivotTable, right-click in the Values area, choose Summarize Values By > More Options > Value Field Settings, and select DISTINCTCOUNT.
- To create a measure in Power Pivot: open Manage (Alt BM), click in a cell in the measure grid, type the name, then a colon and equal sign (:=), then the formula, e.g.
UniqueList := DISTINCTCOUNT([list of names][names])(the table and field names shown in square brackets, as the speaker typed them). - Drag the calculated field from the field list into the PivotTable's Values area.
- The example data has almost 20,000 names, and the unique count shown is 72.
- The speaker notes the PivotTable shortcut is fine for simple DISTINCTCOUNT, but explicit measures are better when you need formatting or reuse.
- The DAX approach is described as only needing Excel 2013 with the data model enabled.
Bottom Line
For counting unique values, DISTINCTCOUNT is the simpler and much faster choice, and Power Pivot relationships are preferable to complex array formulas when helper columns aren't an option.
