See two examples of Array Formulas and two examples of PowerPivot DAX formulas that count unique values in a column (Distinct Count) and compare and contrast:
(00:09) Array Formula For counting unique values using FREQUENCY function and other formula elements. This is more complicated than equivalent DAX function DISTINCTCOUNT.
(01:12) Array Formula For counting unique values using SUMPRODUCT and COUNTIF functions. This is very slow calculating in comparison to FREQUENCY Array Formula and DAX function DISTINCTCOUNT.
(02:07) Access DAX function DISTINCTCOUNT without PowerPivot using Excel Table feature and Create PivotTable dialog box “Add data to Data Model” check box.
(02:32) Convert proper data set to Excel Table feature using Ctrl + T
(02:36) Name Excel Table using Alt, J, T, A
(02:42) Add Table to Data Model using Create PivotTable dialog box “Add data to Data Model” check box
(03;33) Use PivotTable function Distinct Count
(03:55) Open “Manage Data Model” window with Alt, B, M
(04:17) Create Calculated Field (Measure) called Unique Count using the DAX function DISTINCTCOUNT
(04:52) Assignment Operator for Calculated Fields is “Colon and Equal Signs”.
(05:49) Why use Calculated Field (Explicit Formula) to get DISTINCTCOUNT rather than Distinct Count (Implicit Formula) from PivotTable?
(06:27) Create PivotTable that uses Calculated Field to get a unique count.
(07:31) See very complicated Array Formula to simulate Array Formula Helper Columns And/Or Relationships between table in PowerPivot. This formula is very complicated and it would be easier to just use PowerPivot!