MSPTDA 18: DAX Iterators, Table Functions, Grain, Cardinality, Materializing Tables Excel & Power BI
Download Start files: .xlsx Start 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables-Start.xlsx
Zipped Folder with data that was already loaded into previous file: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018Files.zip Download Other Start Files:
.pbix Start 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DoubleCountStart.pbix .xlsx Start 2: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-ALLVALUES-Start.xlsx .pbix Start & Finished 2: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-BigData.pbix
Zipped Folder with data that was already loaded into previous file: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018BigDataTextFiles.zip
Download Finished files: .xlsx Finished 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables-Finished.xlsx .pbix Finished 1: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DoubleCountFinished.pbix .xlsx Finished 2: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-ALLVALUES-Finished.xlsx pdf Notes: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-DAX-IteratorsTables.pdf Assigned Homework: Download Word Document and read: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/HomeworkVideo018.docx Then download the rest of the files and complete the homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-HomeworkFile01Start.pbix Examples of Finished Homework: https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-HomeworkFile01Finished.pbix https://people.highline.edu/mgirvin/AllClasses/348/MSPTDA/Content/DAX/018-MSPTDA-HomeworkFile02Finished.pbix 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