Microsoft Training

DAX FOR EXCEL

WTS-Main-Logo

Learn-Dax for excel

Microsoft Dax for excel

2-DAY CLASS

LENGTH 14 HOUR CLASS TIME

DAX – Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI,
PowerPivot, Power Query and Excel for creating calculated columns, measures, and custom tables. It is a collection of
functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more
values. This 2-day course will introduce users to using this language to create custom calculations to solve business
problems.

Students will learn how to:

  • Write calculated columns, measures, and tables
  • How to visualize the way Power Bi computes DAX calculations
  • How to troubleshoot custom code

 

Who Needs This?

8 reasons to learn Dax for Excel

This course is designed folks that have been using Power Bi to build analytic solutions and are ready to take advantage
of the power and flexibility that DAX allows. Learning DAX is a logical next step for anyone using Power Bi or Power
Pivot and Power Query using the following functions in DAX:

  • Date and Time Functions
  • Filter Functions
  • Information Functions
  • Logical Functions
  • Math and Statistical Functions
  • Table Manipulations Functions
  • Text Functions
  • Time Intelligence Functions

What is Dax?

  • Basics of DAX Syntax
  • Creating a calculated column
  • Creating a calculated measure
  • Creating a calculated table
  • Differences in evaluation context
  • Rules of Evaluation
  • Working with variables
  • Commenting your code
  • Testing your code

Course Outline:

  • Implicit measures
  • Adding quick measures
  • Doing basic math
  • Using logic in your calculations
  • Aggregating and summarizing data
  • Working with DAX data types


WORKING WITH CONTEXT IN THE DATA MODEL

  • Context defined
  • Data modeling basics
  • Introduction to dimensional modeling
  • Relationships and their effect on the
    evaluation context
  • Getting data from other tables using
    RELATED() and RELATEDTABLE()
  • Modifying the context using CALCULATED()
  • Looking up data without using relationships

PERFORMING MORE ADVANCED CALCULATIONS

  • Handling errors gracefully
  • Using DAX iterator functions
  • Using table manipulation functions
  • Troubleshooting your calculations using the
    Performance Analyzer and DAX Studio


WORKING WITH TIME

  • Performing date calculations
  • Working with date tables
  • Generating a date table with the
    CALENDAR() function
  • Defining custom operating periods
  • YTD, QTD, and MTD calculations
  • Customer to-date calculations
  • Finding year-over-year change
  • Finding moving averages


ENHANCING THE USER EXPERIENCE

  • Controlling visibility of your measures
  • Using What-if parameters
  • Adding banding
  • Using DAX to provide row-level security