MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI

Zipped folder with all files for this video: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/FilesUsedInVideo18.zip

Class web site with files for download: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/DAX.htm

pdf Notes: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables.pdf
This video teaches you about the power and pitfalls of DAX Iterator Functions and DAX Table Functions in Excel Power Pivot and in Power BI.
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

Topics:
1. (00:15) Introduction
2. (00:31) Summary of what we know so far in class about Iterators and Table Functions
3. (01:38) Summary of what we will learn in this video about Iterators and Table Functions
4. (02:51) What does DAX stand for? Data Analysis eXpressions
5. (03:36) Introduction to Iterator Functions
6. (04:51) Learn how Iterators Work using SUMX & AVERAGEX Iterating Over Fact Table at Transaction Grain
7. (08:24) Be Careful of Context Transition & Iterating over a Fact Table that "Materialize" Unnecessary Tables.
8. (11:14) DAX Studio to Time DAX Formulas. Including how to use the ROW DAX Function to help time Measures.
9. (18:55) Create PivotTable with Measures and see that the Measure that has to materialize a Fact Table for each row in the iteration takes a LONG time to calculate in a PivotTable.
10. (19:49) Power BI Example of “Double Count” Problem with Context Transition. Be Careful of Context Transition & "Double Count" Problem. Solutions #1: Use Formula rather than Measure.
11. (25:07) Solution #2: Use Power Query to add Primary Key can fix the problem also.
12. (26:11) AVERAGEX at Day Grain use dDate Table. We want Context Transition in this formula and we will Never have the “Double Count” problem because dDate Table has no duplicates.
13. (27:37) Grain of Fact Table or Iterator.
14. (30:18) AVERAGEX at Month Grain with VALUES(Column)
15. (30:18) Introduction to idea that we need DAX Table functions like VALUES to help create the correct Grain for Tables that we can use in Iterator Functions.
16. (31:35) VALUES DAX Function
17. (33:17) DAX Studio to Visualize or Materialize Tables
18. (33:48) CROSSJOIN DAX Function.
19. (35:05) CONCATENATEX and VALUES to list values in the Current Filter Context
20. (40:15) AVERAGEX at Month Grain with CROSSJOIN(VALUES(Column),VALUES(Column))
21. (41:30) Can we reduce "Cardinality"? Try not to Iterate over Fact Table. Alternative Formula for Total Revenue in Power BI Example. Timing formulas in DAX Studio.
22. (48:20) Time DAX Measures from Power BI with DAX Studio.
23. (50:20) Closer look at ALL and VALUES DAX Functions. Discuss the Blank Row that shows up from unmatched item in a relationship.
24. (55:21) Compare and Contrast ALL and VALUES.
25. (56:10) DISTINCT and ALLNOBLANKROW DAX Functions.
26. (57:23) Look at other DAX Table Functions.
27. (58:15) Discussion of FILTER DAX Table Function and CALCULATETABLE DAX Table Function
28. (01:01:00) Look at ADDCOLUMNS DAX Function.
29. (01:01:30) Excel Existing Connections to pull data from Data Model into Excel Sheet.
30. (01:02:54) DAX VALUES Function to pull a variable from an Excel Sheet into the Data Model.
31. (01:05:05) Summary

Видео MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI канала ExcelIsFun