top of page

Microsoft Power BI DAX and Data Modeling

IPA-M102

Microsoft Power BI DAX and Data Modeling

Basic data models are simple to construct using Power BI Desktop. However, Data Analysis Expressions are required if you wish to report across time periods—for example, to display year-over-year growth or to compare sales or other data by month or quarter (DAX). DAX is a programming language for creating Power BI calculations that extend your data model.


Learn how to use DAX aggregate functions and add calculated columns, build measurements, and use DAX logical and filter functions and recommended practices for data model construction and optimization.


Whether you're using Power BI Desktop, Power Pivot in Excel, or Analysis Services, learning Data Analysis Expressions (DAX) is essential to creating effective data models.


Training Duration: 5 Days

OVERVIEW

This workshop is a complete course about the DAX language. DAX is the native language of Power BI, Power Pivot for Excel, and SSAS Tabular models in Microsoft SQL Server Analysis Services.


This Power BI training for DAX is aimed at users of Power BI, Power Pivot for Excel, and at Analysis Services developers that want to learn and master the DAX language. This course covers the latest version of DAX 2019.


This workshop is a complete course about building the most optimal data models for your Power BI reports. It introduces the audience to the basic techniques of shaping data models in Power BI. It offers many real-world examples that will help you look at your reports in a different way – pretty much like experienced data modelers do.

OBJECTIVES

After completing this course, students will be able to:

  • Understand all the features of the DAX language

  • Write formulas for common and advanced scenarios

  • Learn the vast techniques of shaping and building a correct data model to be used in Power BI.

  • Understand the common challenges and mistakes that people have in creating a data model.

  • Learn the most optimum way of building a sustainable data model for Power BI reports

PREREQUISITES

Attendees need to have a basic knowledge of the data modeling in Power Pivot for Excel, or Power BI Desktop, or Analysis Services Tabular modeling.

AUDIENCE

The intended audience would be for those who have taken the Microsoft Power BI course and would love to continue to explore formulas and calculations in Microsoft Power BI.

COURSE MODULES

Module 1: Introduction to DAX

  • What is DAX?

  • DAX data types

  • Calculated columns

  • Measures

  • Aggregation functions

  • Counting values

  • Conditional functions

  • Handling errors

  • Using variables

  • Mathematical functions

  • Relational functions


Module 2: Table Functions

  • Introduction to table functions

  • Filtering a table

  • Ignoring filters

  • Mixing filters

  • DISTINCT Function

  • How many values for a column?

  • ALLSELECTED function

  • RELATEDTABLE function

  • Tables and relationships

  • Tables with one row and one column

  • Table variables


Module 3: Evaluation Contexts

  • Introduction to evaluation contexts

  • Filter context

  • Row context

  • Context errors

  • Filtering a table

  • Using RELATED in a row context

  • Ranking by price

  • Evaluation contexts and relationships

  • Filters and relationships


Module 4: CALCULATE Function

  • Introduction to CALCULATE function

  • CALCULATE function examples

  • CALCULATE function recap

  • What is a filter context?

  • KEEPFILTERS function

  • CALCULATE operators

  • Use one column only in a compact syntax

  • Variables and evaluation contexts


Module 5: Iterators

  • Working with iterators

  • MINX and MAXX functions

  • Useful iterators

  • RANKX function

  • ISINSCOPE function


Module 6: Time Intelligence in DAX

  • What is time intelligence?

  • Time intelligence functions

  • DATEADD function

  • DATESINPERIOD function

  • Running total