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

Overview

Dive deep into DAX iterator functions and table functions for Excel Power Pivot and Power BI in this comprehensive video tutorial. Learn about the power and potential pitfalls of these functions, including SUMX, AVERAGEX, VALUES, CROSSJOIN, and more. Explore context transition, materialization of tables, and strategies to optimize performance. Practice timing DAX formulas using DAX Studio and understand how to handle common issues like the "Double Count" problem. Gain insights into working with different data grains, reducing cardinality, and effectively using functions like ALL, DISTINCT, and FILTER. By the end, master techniques for pulling data between Excel sheets and the Data Model, enhancing your ability to create efficient and powerful data analysis solutions.

Syllabus

) Introduction.
) Summary of what we know so far in class about Iterators and Table Functions.
) Summary of what we will learn in this video about Iterators and Table Functions.
) What does DAX stand for? Data Analysis eXpressions.
) Introduction to Iterator Functions.
) Learn how Iterators Work using SUMX & AVERAGEX Iterating Over Fact Table at Transaction Grain.
) Be Careful of Context Transition & Iterating over a Fact Table that "Materialize" Unnecessary Tables..
) DAX Studio to Time DAX Formulas. Including how to use the ROW DAX Function to help time Measures..
) 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..
) 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..
) Solution #2: Use Power Query to add Primary Key can fix the problem also..
) 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..
) Grain of Fact Table or Iterator..
) AVERAGEX at Month Grain with VALUES(Column).
) 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..
) VALUES DAX Function.
) DAX Studio to Visualize or Materialize Tables.
) CROSSJOIN DAX Function..
) CONCATENATEX and VALUES to list values in the Current Filter Context.
) AVERAGEX at Month Grain with CROSSJOIN(VALUES(Column),VALUES(Column)).
) 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..
) Time DAX Measures from Power BI with DAX Studio..
) Closer look at ALL and VALUES DAX Functions. Discuss the Blank Row that shows up from unmatched item in a relationship..
) Compare and Contrast ALL and VALUES..
) DISTINCT and ALLNOBLANKROW DAX Functions..
) Look at other DAX Table Functions..
) Discussion of FILTER DAX Table Function and CALCULATETABLE DAX Table Function.
) Look at ADDCOLUMNS DAX Function..
) Excel Existing Connections to pull data from Data Model into Excel Sheet..
) DAX VALUES Function to pull a variable from an Excel Sheet into the Data Model..
) Summary.