Excel Business Intelligence: Power Pivot and DAX

4h 2mIntermediate2022-03-10

Authors

Chris Dutton

Certified Microsoft Excel Expert, Analytics Consultant

Course details

Data Analysis Expressions (DAX) allow Microsoft Excel users to create powerful calculated fields in Power Pivot. In this course—the third and final installment of the Excel Business Intelligence series—experienced Excel trainer Chris Dutton spells out how to use the DAX language and Power Pivot to explore and analyze relational data models. Chris picks up where he left off in the previous installment of the series, demonstrating how to add calculated columns and measures to data models in Excel. He highlights the differences between calculated columns and DAX measures, covers common DAX formulas and functions, and discusses best practices for working with DAX and Power Pivot.

Learning objectives
Examine troubleshooting options affecting Excel performance while using Power Pivot.
Explain the differences between Power Pivot and other Excel PivotTables.
Describe the functionality of calculated columns.
Determine the appropriate use of functions in DAX formulas.
Identify results returned by using the CALCULATE function.
Review the use of slicers with Power Pivot.

Skills covered

Business IntelligenceSpreadsheetsMicrosoft ExcelData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftOne-Off

Concepts

0. Introduction

01 - Welcome

1. Getting Started

02 - Important - Versions and compatibility
03 - Set expectations

2. Power Pivot and DAX 101

04 - Introduction to Power Pivot and DAX
05 - Create a Power Pivot table
06 - Power Pivots versus normal pivots
07 - Introduction to Data Analysis Expressions (DAX)
08 - Calculated columns
09 - DAX measures
10 - Create implicit measures
11 - Create explicit measures (AutoSum)
12 - Create explicit measures (Power Pivot)
13 - Understand filter context
14 - Step-by-step measure calculation
15 - Recap - Calculated columns versus measures
16 - Power Pivot best practices

3. Common DAX Functions

17 - Introduction to DAX functions
18 - DAX formula syntax and operators
19 - Common DAX function categories
20 - Basic math and stats functions
21 - COUNT, COUNTA, DISTINCTCOUNT, and COUNTROWS
22 - Logical Functions (IF, AND, and OR)
23 - Switch and Switch (TRUE)
24 - Text functions
25 - The CALCULATE function
26 - Add filter context with FILTER - Part 1
27 - Add filter context with FILTER - Part 2
28 - Remove filter context with ALL
29 - Join data with RELATED
30 - Iterator ( X ) functions - SUMX
31 - Iterator ( X ) functions - RANKX
32 - Basic date and time functions
33 - Time intelligence formulas
34 - Speed and performance considerations
35 - DAX best practices

Conclusion

36 - Final section
37 - Data visualization options
38 - Wrapping up