) Introduction to building Data Models with PowerPivot.
) Topics in this video.
) What is PowerPivot?.
) What versions of Excel have PowerPivot? Notes for Excel 2013 and Excel 2016.
) What is the Data Model?.
) What DAX stands for (Data Analysis Expressions).
) What is Data Modeling?.
) What are Related Tables?.
) References for more advanced PowerPivot and Data Modeling.
) Where to find notes in the downloadable PowerPoint about DAX formulas and Calculated Columns and Measures (Calculated Fields).
) IMPORTANT if you are NEW to PowerPivot: Remind ourselves how we build Data Models in Excel all the while comparing what we are used to doing in Excel and how it will change when we get to PowerPivot.
) Step 1 in Building Data Model: Import Proper data sets into PowerPivot Data Model: 1) Convert tables to Excel Tables (Ctrl + T and Alt, J, T, A), 2) Click Add to Data Model button in PowerPivot Ribbon Tab..
) Intro to Manage Data Model window.
) Step 2 in Building Data Model: Build Relationships using Diagram View. Simply drag and drop fields from dimension table to fact table..
) Step 3 in Building Data Model: Build DAX Calculated Columns.
) DAX function: RELATED (substitute for VLOOKUP).
) Introduction to Table Names and Field Names (Field name in square brackets) in DAX formulas.
) How formulas are copied in a Calculated Column.
) Convention for building DAX formulas: 1) When you use a column name (field name) in a formula use both Table Name AND Field Name, when you use a Measure (Calculated Field) use only the Measure Name in Square Brackets..
) DAX function: ROUND.
) Row Context for DAX Calculated Columns.
) Example of Implicit Formula in a PowerPivot Data Model PivotTable. Why we should not use Implicit Formulas..
) Step 3 in Building Data Model: Build DAX Calculated Columns and Measures (Calculated Fields). This will not be an Implicit Formula. It will be an Explicit Formula..
) The Implicit “Sum of Net revenue” Formula shows up in Formula Field Dropdown List: DO NOT use it!.
) Add Number Formatting to our Measures (Calculated Fields)..
) Create PivotTable Report using our new DAX Measures (Calculated Fields)..
) Filter Context for DAX Measures (Calculated Fields)..
) Why we should not use Implicit Formulas AND Why we should use Explicit Formulas AND why DAX Measures (Calculated Fields) Calculate quickly on Big Data: Filter Context cause DAX Measures (Calculated Fields) to filter the underlying table so it has to process on a smaller table than the full-unfiltered table..
) Summary and Conclusion.