Excel 2013 PowerPivot Basics #04: DAX Formulas Can Be Easier & Faster Than Array Formulas

Download file: http://people.highline.edu/mgirvin/excelisfun.htm Scroll to down to PowerPivot Section. 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: 1. (00:09 min mark) Array Formula For counting unique values using FREQUENCY function and other formula elements. This is more complicated than equivalent DAX function DISTINCTCOUNT. 2. (01:12 min mark) Array Formula For counting unique values using SUMPRODUCT and COUNTIF functions. This is very slow calculating in comparison to FREQUNENCY Array Formula and DAX function DISTINCTCOUNT. 3. (02:07 min mark) Access DAX function DISTINCTCOUNT without PowerPivot using Excel Table feature and Create PivotTable dialog box “Add data to Data Model” check box. 4. (02:32 min mark) Convert proper data set to Excel Table feature using Ctrl + T 5. (02:36 min mark) Name Excel Table using Alt, J, T, A 6. (02:42 min mark) Add Table to Data Model using Create PivotTable dialog box “Add data to Data Model” check box 7. (03;33 min mark) Use PivotTable function Distinct Count 8. (03:55 min mark) Open “Manage Data Model” window with Alt, B, M 9. (04:17 min mark) Create Calculated Field (Measure) called Unique Count using the DAX function DISTINCTCOUNT 10. (04:52 min mark) Assignment Operator for Calculated Fields is “Colon and Equal Signs”. 11. (05:49 min mark) Why use Calculated Field (Explicit Formula) to get DISTINCTCOUNT rather than Distinct Count (Implicit Formula) from PivotTable? 12. (06:27 min mark) Create PivotTable that uses Calculated Field to get a unique count. 13. (07:31 min mark) 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!