Microsoft Power BI DAX – Advanced

Duration: 1 day

This course will cover the ability to use Data Analysis Expressions (DAX) language to perform powerful data manipulations within Power BI Desktop.

Learning how to create effective DAX formulas will help you get the most out of your data. DAX is a powerful language for manipulating Power BI data, but it can be a challenge to get the calculations you want.

Understanding DAX is the key to getting the information you need so you can begin to solve real business problems that affect your bottom line. This is Business Intelligence, and DAX will help you get there.

To get the most out of this course

We recommend that you attend our 1-day Microsoft Power BI Introduction to DAX course prior to this course. Otherwise you should have some experience of DAX and its calculations and want to take your knowledge to the next level.

Outline of our Advanced DAX for Microsoft Power BI Course - DURATION: 1 - DAY

Evaluation Context

  • What is Evaluation Context?
  • The difference between evaluations using Row Context and evaluations using Filter Context

CALCULATE Function

  • Recap on the CALCULATE function
  • Using complex filters within CALCULATE using FILTER

Removing Filters

  • Using ALL, ALLSELECTED & ALLEXCEPT Functions

VALUES & DISTINCT Functions

  • The difference between VALUES and DISTINCT
  • Looking at the “Blank” entry
  • Using ALLNOBLANKROW

Activating Inactive Relationships

  • The problem of Inactive Relationships
  • Using the USERELATIONSHIP function

Working with Many-to-Many Relationships

  • Understanding “Conventional” Many-to-May relationships between Dimensions
  • Creating a “Bridge Table”
  • Using the CROSSFILTER function
  • Looking at Many-to-Many based on Cardinality
  • Understanding Granularity
  • Looking at the TREATAS Function
  • Understanding “Weak” relationships

Using Variables

  • The VAR and RETURN statements
  • When to use Variables

Advanced Filter Context

  • Understanding Context Transition
  • Understanding Expanded Tables

Advanced Calculated Columns

  • Problems with Circular Dependencies
  • Using the EARLIER Function

Advanced Table Functions

  • Using SUMMARIZECOLUMNS
  • Using CROSSJOIN

Putting it All Together (Time permitting)

    This session is a “workshop” that explores using DAX to solve a variety of data analysis problems, for example:

  • Context Transition.
  • Finding TopN Percent
  • Binning Measures into numeric categories
  • Comparing data in 2 different time frames
  • Finding New and Returning customers
  • Of the customers who bought X, who also bought Y

Speak to a trainer now

0800 0199 746 for any questions about the courses we offer