Course Overview for Mastering DAX in Power BI Desktop for Business Analysis and Reporting
This 2-day course is designed to help participants understand and apply DAX (Data Analysis Expressions) in Power BI Desktop. Participants will learn how to create calculated columns, measures, business logic, text and date transformations, and advanced analytical calculations using the CALCULATE function and Time Intelligence functions.
The course combines theory, demonstrations, and hands-on exercises to ensure participants can confidently apply DAX formulas in real-world reporting and dashboard scenarios.
Course Objectives
By the end of this course, participants will be able to:
- Understand the purpose of DAX in Power BI Desktop.
- Explain basic data modelling concepts and table relationships.
- Create calculated columns using DAX functions.
- Apply date, text, and logical functions in Power BI.
- Understand the difference between calculated columns and measures.
- Explain row context and filter context in DAX.
- Create explicit measures for business calculations.
- Use the CALCULATE function to change filter context.
- Apply Time Intelligence functions for period-based analysis.
- Build practical Power BI reports using DAX calculations.
Course Content
Day 1: DAX Fundamentals and Core Functions
Module 1: Introduction to DAX
Description:
This module introduces participants to DAX and explains how it is used in Power BI Desktop to create business calculations, enhance data models, and support analytical reporting.
Topics Covered
- What is DAX in Power BI?
DAX is the formula language used in Power BI to perform calculations on data. Participants will learn where DAX is used, such as calculated columns, measures, and tables.
- Introduction to Data Modelling
Participants will learn the basic concept of data modelling, including tables, relationships, fact tables, dimension tables, and how relationships affect DAX calculations.
- First DAX Function: RELATED Function
The RELATED function is used to retrieve values from another related table. Participants will learn how to use it to bring descriptive information into a table through existing relationships.
Module 2: Date Functions
Description:
This module focuses on commonly used DAX date functions. Participants will learn how to extract date parts, calculate date differences, and create date-based fields for reporting and analysis.
Topics Covered
- DAY Function
Extracts the day number from a date, useful for daily-level analysis.
- MONTH Function
Returns the month number from a date, useful for monthly reporting and grouping.
- YEAR Function
Extracts the year from a date, commonly used for yearly analysis and filtering.
- QUARTER Function
Returns the quarter number of the year, useful for quarterly business reporting.
- WEEKNUM Function
Identifies the week number of a date, allowing weekly trend analysis.
- WEEKDAY Function
Returns the day of the week, useful for analysing weekday versus weekend patterns.
- TODAY Function
Returns the current date, commonly used for dynamic calculations such as aging and due dates.
- NOW Function
Returns the current date and time, useful for real-time timestamp calculations.
- DATEDIFF Function
Calculates the difference between two dates in days, months, years, or other intervals.
- DATEADD Function
Shifts dates forward or backward by a specified interval, often used in time comparison analysis.
- STARTOFMONTH Function
Returns the first date of the month, useful for monthly period calculations.
- ENDOFMONTH Function
Returns the last date of the month, useful for month-end reporting and analysis.
Module 3: Text Functions
Description:
This module teaches participants how to clean, transform, extract, and combine text values using DAX text functions. These functions are useful when preparing data for reports and improving data readability.
Topics Covered
- UPPER Function
Converts text into uppercase format for consistency.
- LOWER Function
Converts text into lowercase format for standardisation.
- PROPER Function
Capitalises the first letter of each word, useful for formatting names and labels.
- LEFT Function
Extracts characters from the beginning of a text string.
- RIGHT Function
Extracts characters from the end of a text string.
- MID Function
Extracts characters from the middle of a text string based on position.
- TRIM Function
Removes extra spaces from text values to improve data quality.
- LEN Function
Counts the number of characters in a text string.
- CONCATENATE Function
Combines two text values into one text string.
- Ampersand Operator
Uses the & symbol to join multiple text values in a simpler and flexible way.
- SEARCH Function
Finds the position of a specific character or word within a text string.
- SUBSTITUTE Function
Replaces existing text with new text, useful for correcting or standardising data values.
Module 4: Logical Functions
Description:
This module introduces logical functions used to create conditional calculations in Power BI. Participants will learn how to apply business rules and decision-making logic using DAX.
Topics Covered
- IF Function
Returns different results based on whether a condition is true or false.
- IFERROR Function
Handles errors in calculations by returning an alternative result when an error occurs.
- AND Function
Checks whether multiple conditions are all true.
- OR Function
Checks whether at least one of multiple conditions is true.
Day 2: Advanced DAX, CALCULATE and Time Intelligence
Module 5: Advanced DAX Functions and Concepts
Description:
This module introduces important DAX concepts that help participants understand how calculations behave in Power BI. Participants will learn the difference between calculated columns and measures, as well as how context affects results.
Topics Covered
- New Columns vs Measures
- Calculated Column
A calculated column creates a new column in a table using a DAX formula. It is calculated row by row and stored in the data model.
- Measures
Measures perform dynamic calculations based on filters and visuals. They are commonly used for totals, KPIs, percentages, and aggregations.
iii. Measures vs Calculated Column
Participants will learn when to use a measure and when to use a calculated column based on reporting needs, performance, and calculation behaviour.
- Row Context vs Filter Context
Participants will learn how DAX evaluates formulas differently depending on row-level calculations and report-level filters.
- Implicit vs Explicit Measures
Participants will understand the difference between automatic calculations created by Power BI and custom measures created using DAX formulas.
Module 6: CALCULATE Function
Description:
This module focuses on CALCULATE, one of the most important and powerful functions in DAX. Participants will learn how CALCULATE changes filter context to create advanced business calculations.
Topics Covered
- SUM Function
Adds numeric values together and is commonly used for total sales, total quantity, or total revenue calculations.
- YEAR Function
Extracts the year value from a date and can be used inside filter conditions for year-based analysis.
- COUNT Function
Counts the number of values or records in a column, useful for transaction counts or customer counts.
- AVERAGE Function
Calculates the average value of a numeric column, useful for average sales, average cost, or average performance analysis.
Module 7: CALCULATE Time Intelligence Functions
Description:
This module introduces Time Intelligence functions used to compare business performance across different time periods. Participants will learn how to create previous period, next period, and year-over-year calculations.
Topics Covered
- PREVIOUSDAY Function
Returns the previous day based on the current date context, useful for daily comparison.
- PREVIOUSMONTH Function
Returns the previous month period, commonly used for month-over-month analysis.
- PREVIOUSQUARTER Function
Returns the previous quarter period, useful for quarterly performance comparison.
- PREVIOUSYEAR Function
Returns the previous year period, useful for year-over-year reporting.
- NEXTDAY Function
Returns the next day based on the current date context.
- NEXTMONTH Function
Returns the next month period, useful for forward-looking comparisons.
- NEXTQUARTER Function
Returns the next quarter period, useful for future period analysis.
- NEXTYEAR Function
Returns the next year period, useful for future year comparison or forecasting scenarios.
- SAMEPERIODLASTYEAR Function
Returns the same period from the previous year, commonly used for year-over-year comparison.
