MS Power BI Advanced – Create Performance Measures with DAX
This course is designed to develop your skills in DAX (Data Analysis Expressions) language to enhance data models, through the use of formulas and expressions. Power BI users who are want to get even more out of this popular tool will learn how to write code in DAX (Data Analysis Expressions) to enhance the data models, create custom performance measures, and create complex calculations. Learn how to incorporate these results into practical stunning reports to make decision making easier.
Course price includes:
Perth’s best IT trainers at Perth’s best CBD training centre
Free help desk support after your training
Interactive, practical and hands on learning
Comprehensive take-home training and reference manual with exercise files
Morning and afternoon refreshments, delicious café lunch
Overview of using DAX to create Performance Measures
Types of calculations (table, column, measure)
Data types and syntax
Using DAX to calculate columns in Row Context
Getting values from related tables using the RELATED function
The DIVIDE function
When calculated columns don’t work
Overview and guidelines for creating measures
Aggregator Functions – SUM, AVERAGE, etc
Using Row Context and Filter Context
Creating a measures table
Using measures instead of calculated columns
Iterator functions – SUMX, AVERAGEX, etc
Contexts, relationships and filter propagation
Implicit Vs Explicit measures
Basic DAX Functions
Common maths functions
Date-times in DAX and common Date-time functions
Common text functions
Using the FILTER function
Visualising table function results
ALL and ALLECEPT
VALUES and DISTINCT
Logical and Branching Functions
The IF function
AND, OR and NOT
The SWITCH function
The IFERROR function
The CALCULATE Function
CALCULATE and FILTER
Conditions in CALCULATE
Time Intelligence Functions
Overview of Time Intelligence functions
Built-in time intelligence functions
Creating … ToDate functions
The DATEADD and PARALLELPERIOD functions
Period comparison functions (eg Month on Month)
Adding a ‘what if’ parameter
Creating and using Parameter tables
The SELECTEDVALUE function
Understand some of the options for data model schemas and table structures.
Understand how to work with different relationship types, including active and inactive, and how filter propagation works.
Understand and gain skills in incorporating disconnected table at higher levels (like budgets) and additional data tables into the model.
Understand DAX, the types of calculations available and its contexts, as well as gaining experience in writing DAX and formatting for easier readability and future modification.
Practical skills in using core DAX functions to create a range of typical, real-world performance measures.
Understanding of the main DAX ‘time intelligence’ functions and gaining skills in writing typical and custom time intelligence functions.
This is a technical course and requires extensive use of excel-like functions. You will require the following prerequisite knowledge and experience to successfully understand and apply the concepts:
Have experience in creating visualisations in Power BI.
Have experience in using Power Query to transform data into models.
Understand the basic database theory of One-to-Many relationships.
Have experience in using basic DAX in Power BI to create tables and add columns to tables.
Have competency in using Excel formulas and functions similar to the level of Excel Formulas & Functions
Here’s an example of a formula that you would need to be able to understand:
IF(A1 > TODAY(), VLOOKUP(A2, SalaryTable, 2, FALSE), IF( A1 = TODAY(), VLOOKUP(A2, SalaryTable, 3, FALSE), VLOOKUP(A2, SalaryTable, 4, FALSE)))