Description

DAX, short for Data Analysis eXpressions is the language behind Power BI. It’s a powerful language that’s evaluated through the filter context of the Power BI Data Model. This “Filter context” can cause new users to experience problems with DAX until they learn how it works. Once mastered, creating effective DAX formulas will help you get the most out of your data. When you get the information you need, 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 from this course

Attendees don’t need any prior DAX knowledge but they should have experience of Microsoft Power BI, and familiarity with Excel Functions like SUMIFS and VLOOKUP. People with Database, Programming or other BI experience will be qualified for the course even if they don't have Excel experience. We recommend that you attend our 2-day Microsoft Power BI course prior to taking this course

What you'll learn

Importing Your Data and Creating the Data Model

  • Overview of importing data into the Power BI Desktop and creating the Data Model.

 Using DAX

  • Syntax used by DAX.
  • Understanding DAX Data Types.

 Creating Calculated Columns

  • How to use DAX expressions in Calculated Columns.
  • Using AND and OR operators (&& and ||).
  • Using BLANK and ISBLANK.
  • Using RELATED to look up values from related tables.
  • Understanding de-normalisation.

 Creating Measures

  • Why use Measures as opposed to Calculated Columns?
  • Implicit and Explicit Measures.
  • How to Create Measures using SUM & AVERAGE
  • Using COUNTROWS and DISTINCTCOUNT.
  • Using DAX aggregate functions; SUMX, AVERAGEX etc

 Evaluation Context

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

Working with Table Functions

  • Overview of DAX Functions that output Tables.
  • Using ALL, FILTER and VALUES functions.
  • Creating Parameter Tables.

 CALCULATE Function

  • Exploring the importance of the CALCULATE function.
  • Using complex filters within CALCULATE using FILTER.
  • Using ALLSELECTED Function.

 Time Intelligence Functions

  • Why Time Intelligence Functions?
  • Creating a Date Table.
  • Finding Month to Date, Year To Date, Previous Month and Same Period Last Year.
  • Creating Moving Annual Totals and Moving Averages.

 Working with Many-to-Many Relationships

  • The problem of Many-to-Many relationships.
  • Creating a “Bridge Table”.
  • Exploring the concept of “Expanded Tables”.

 Advanced Calculated Columns

  • Using CALCULATE in a Calculated Column.
  • Context Transition.
  • Using COUNTROWS and FILTER in Calculated Columns.
  • How the EARLIER function can be used in Row Context.