Excel Business Intelligence: Power Pivot and DAX

Learn how to use Power Pivot and the DAX language to explore and analyze relational data models in Excel.

Introduction

• Welcome

1. Getting Started

• Important: Versions and compatibility
• Set expectations

2. Power Pivot and DAX 101

• Introduction to Power Pivot and DAX
• Create a Power Pivot table
• Power Pivots versus normal pivots
• Introduction to Data Analysis Expressions (DAX)
• Calculated columns
• DAX measures
• Create implicit measures
• Create explicit measures (AutoSum)
• Create explicit measures (Power Pivot)
• Understand filter context
• Step-by-step measure calculation
• Recap: Calculated columns versus measures
• Power Pivot best practices

3. Common DAX Functions

• Introduction to DAX functions
• DAX formula syntax and operators
• Common DAX function categories
• Basic math and stats functions
• COUNT, COUNTA, DISTINCTCOUNT, and COUNTROWS
• Logical Functions (IF, AND, and OR)
• Switch and Switch (TRUE)
• Text functions
• The CALCULATE function
• Add filter context with FILTER: Part 1
• Add filter context with FILTER: Part 2
• Remove filter context with ALL
• Join data with RELATED
• Iterator ("X") functions: SUMX
• Iterator ("X") functions: RANKX
• Basic date and time functions
• Time intelligence formulas
• Speed and performance considerations
• DAX best practices

Conclusion

• Final section
• Data visualization options
• Wrapping up