This two-day instructor-led course introduces participants to Data Analysis Expressions (DAX), the powerful expression language used to create custom calculations within Power BI's data model. While the curriculum covers theoretical foundations of data modeling and DAX fundamentals, the primary focus remains on applying DAX to address real-world business challenges. Participants will develop skills in writing calculated columns, measures, and tables; visualizing Power BI's calculation processes; and troubleshooting custom implementations. The course combines structured lectures, practical demonstrations, and hands-on laboratory exercises to ensure a comprehensive understanding.
Software and Setup:
- The standard laboratory configuration for this class includes a Windows 10 personal computer with Power BI Desktop and DAX Studio applications installed.
Target Audience:
- This course is designed for business professionals who have leveraged Power BI to develop analytical solutions and are prepared to harness the advanced capabilities that DAX offers. For experienced Power BI users, learning DAX represents a natural and highly valuable progression in their analytical toolkit.
Prerequisites:
- While hands-on experience with Power BI Desktop is beneficial, it is not strictly mandatory for this class. Students with prior familiarity will maximize their engagement with the laboratory components of the curriculum.
DAX Functions Covered in This Course:
- Date and Time Functions: CALENDAR(), DATE(), DATEDIFF(), NOW(), WEEKDAY(), WEEKNUM(), YEAR(), MONTH(), DAY()
- Filter Functions: ALL(), ALLCROSSFILTERED(), ALLEXCEPT(), ALLSELECTED(), CALCULATE(), CALCULATETABLE(), FILTER(), KEEPFILTERS(), LOOKUPVALUE(), REMOVEFILTERS()
- Information Functions: CONTAINS(), HASONEFILTER(), HASONEVALUE(), ISBLANK(), ISEMPTY(), ISERROR(), USERNAME()
- Logical Functions: AND(), COALESCE(), IF(), IF.EAGER(), IFERROR(), NOT(), OR(), SWITCH(), TRUE(), FALSE()
- Math and Statistical Functions: DIVIDE(), SUM(), SUMX(), AVERAGE(), AVERAGEX(), COUNT(), COUNTBLANK(), COUNTROWS(), COUNTX(), DISTINCTCOUNT(), MAX(), MAXX(), MEDIAN(), MEDIANX(), MIN(), MINX(), RANKX()
- Table Manipulation Functions: CROSSJOIN(), DATATABLE(), NATURALINNERJOIN(), VALUES()
- Text Functions: CONCATENATEX(), FIND(), LEFT(), LEN(), MID(), REPLACE(), SEARCH(), TRIM(), VALUE()
- Time Intelligence Functions: DATEADD(), DATESBETWEEN(), DATESMTD(), DATESQTD(), DATESYTD(), PARALLELPERIOD(), SAMEPERIODLASTYEAR(), TOTALMTD(), TOTALQTD(), TOTALYTD()
Course Outline
Getting Started with DAX
- Understanding what DAX is and its role in Power BI
- Fundamentals of DAX syntax and structure
- Creating calculated columns and their applications
- Creating calculated measures for aggregations
- Creating calculated tables for dynamic data
- Exploring evaluation context and its impact
- Understanding rules of evaluation and order of operations
- Working with variables to simplify complex expressions
- Commenting code for clarity and maintenance
- Testing code for correctness and performance
Performing Basic Calculations
- Using implicit measures and their behavior
- Adding quick measures with built-in templates
- Performing basic mathematical operations
- Incorporating logic and conditional statements
- Aggregating and summarizing data effectively
- Working with DAX data types and type conversion
Working with Context in the Data Model
- Defining context and its importance in calculations
- Data modeling basics and best practices
- Introduction to dimensional modeling concepts
- Understanding relationships and their evaluation context effects
- Retrieving data from related tables using RELATED and RELATEDTABLE functions
- Modifying context using CALCULATE for advanced scenarios
- Looking up data without using explicit table relationships
Performing More Advanced Calculations
- Handling errors gracefully with error management functions
- Using DAX iterator functions for row-by-row processing
- Leveraging table manipulation functions for complex logic
- Troubleshooting calculations using Performance Analyzer and DAX Studio tools
Working with Time
- Performing date calculations and date arithmetic
- Working with date tables and time dimensions
- Generating date tables dynamically with the CALENDAR() function
- Defining custom operating periods and fiscal calendars
- Calculating year-to-date, quarter-to-date, and month-to-date values
- Creating custom to-date calculations for unique business requirements
- Finding year-over-year change and growth analysis
- Finding and calculating moving averages and trends
Enhancing the User Experience
- Controlling visibility of measures and calculated fields
- Using What-if parameters for interactive analysis
- Adding banding for visual formatting and conditional logic
- Using DAX to provide row-level security and data protection
