Contact Us 978.250.4983

Analyzing Data using DAX and M Power Query

Duration: 14 hours

Prerequisites: Familiarity with database concepts, and prior completion of the Analyzing and Presenting Data with Power BI course

Students Will Learn:

  • Using DAX to add new Calculations to an existing data model
  • Simulating a Star data Schema from an OLTP source with DAX
  • Fixing Data Granularity issues with DAX Calculations
  • Providing Row Level security using Row Filters in DAX
  • Performing Transformations while loading data with Power Query and M
  • Using M language to write more powerful queries than the standard Power Query user interface allows

Course Description:

This course is an extension of the Analyzing and Presenting Data with Power BI course, and focuses on using DAX and M Power Query to increase the flexibility and data analytics capabilities of Power BI reports.

Students will learn beginner and intermediate techniques for adding calculations to their Power BI Data models using DAX. Our hands-on labs provide practical exercises that cover some of the common data modeling problems that users face, and how DAX can be used to solve those. Students will experience flattening out an OLTP database into a star schema by using DAX, resolving common granularity issues with data models, and learn how Measures can be used to solve advanced calculation problems that languages like SQL are unable to tackle.

Students will also learn about the M language that Power Query uses behind its graphical user interface, and how writing M directly can enable advanced queries against the data-sources that populate a model with its data.

Course Overview:

DAX Fundamentals
  • DAX Capabilities and Common DAX Use Cases
  • DAX Formula Syntax Fundamentals
  • Formula Evaluation Order
  • DAX Operators
  • DAX Operator Precedence
  • DAX Data Types
  • DAX Type Conversions
  • DAX Functions
  • Nested Function Execution Order
  • Naming Requirements
Basic DAX Calculations
  • Calculated Columns
  • Evaluation Context and Row Context
  • Working with Relationships
  • Using Ad-hoc Relationships with USERELATIONSHIP()
  • Flattening Models with Calculated Columns
  • Calculated Tables
Advanced DAX Calculations
  • Measure Use Cases
  • Implicit Measures
  • Calculated Measures
  • Evaluation Context and Filter Context
  • Implicit Filter Context
  • Explicit Filter Context
  • Filtering with Calculate() and Filter()
  • Undoing Filters with All()
  • Iterator Fundamentals
  • Working with built-in Iterator Functions
  • Using Nested Iterators
DAX and Modeling
  • Resolving Model Granularity Issues
  • Flattening Models
  • Row Level Security with Row Filters
  • Avoiding Ambiguous Relationships with Role Playing Tables
M and Power Query
  • Power Query Basics
  • Using the Formula Bar in Power Query
  • Using the Advanced Editor in Power Query
  • PowerQuery's Standard Function Library
  • M Language
  • Case Sensitivity in Power Query and M
  • Single Literal Values
  • Intrinsic Values
  • Structured Values: Lists, Records, and Tables
  • M Language Operators
  • Commenting Code in M
  • Using the Let Statement
  • Variable Naming in M
  • M's Each Statement
  • Creating Columns in M
  • Conditional Constructs in M
  • M Functions

Related Bootcamps:

Track Duration Price
Master SQL Server Developer 5-course track
6-course track
SQL Server Business Intelligence Developer 4-course track $4,800
Copyright© Developer Bootcamp