98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPower BI DAX
Face to face / Online public schedule & onsite training. Restaurant lunch included at STL venues.
From £460 List price £700
- 1 day Instructor-led
- Courses never cancelled
- Restaurant lunch
Syllabus
Who is this course for?
This one day course is ideal for experienced users of Power BI who wish to optimise the speed and efficiency of Power BI through the use of DAX (Data Analysis Expressions) formula language.
Prerequisites
Attended our 2 day Power BI Modelling, Visualisation and Publishing course, or Power Bi users with a basic understanding of DAX.
Benefits
At the end of this course you will have a greater understanding of building DAX functions. You will understand how you can optimise the speed of your measures in your data model and the use of variables to optimise and debug the DAX measures.Course Syllabus
Introduction to DAX
What is DAX (Data Analysis eXpressions)
Why do we need DAX in Power Bi Desktop
The DAX Structure
The DAX Formatter
Column Calculations versus Measures
Basic DAX functions
SUM/SUMX
COUNT/COUNTX/DISTINCTCOUNT/COUNTROWS
AVERAGE/AVERAGEX
MAX/MAXX
MIN/MINX
Logical functions
IF
SWITCH
AND/OR
Filter functions
CALCULATE
RELATED
RELATEDTABLE
FILTER
HASONEVALUE
VALUES
ALL
USERELATIONSHIP
ISFILTERED
Time-intelligence functions
DATEADD
SAMEPERIODLASTYEAR
EOMONTH
DATESBETWEEN
TOTALMTD/TOTALQTD/TOTALYTD
DATESMTD/DATESQTD/DATESYTD
PREVIOUSDAY/ PREVIOUSMONTH/ PREVIOUSQUARTER/ PREVIOUSYEAR
Other useful functions
LOOKUPVALUE
EARLIER
COMBINEVALUES
SUMMARIZE
ROLLUP
DIVIDE
TOPN
FIRSTDATE
LASTDATE
Understand the use of parameter tables
What is a parameter table
How to use a parameter table in DAX
The DAX Studio Application Demonstration
Benefits of writing DAX and testing DAX in DAX Studio
Variables
What is a variable and what is the benefit using variables
Debug code
Introduction to calculated tables in Power BI Desktop
What is calculated tables
Create Calendar table
The UNION function
Prices & Dates
What you get
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Online training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
University of London
Jamal Ahmed,
Business Intelligence Analyst
Excellent session, lucky to have been the only person joining the training so was able to work at my own pace.
Power BI DAX
LCR Property
Nicola Shaw,
AP & Treasury Manager
There is nothing - it was brilliant!
Power BI DAX
PRS
Richard Snarey,
Director
Jens is a genius !
Power BI DAX
Learning & Development Resources
Blog
- 8 Major Benefits of Microsoft Power BI You Must Know
- Power BI - What is DAX?
- Power BI - Use Variables to Improve Your DAX Formulas
- Power BI’s Mysterious Calculate Function: 1 - Row Context
- Power BI’s Mysterious Calculate Function 2: Filter Context
- Power BI Mysterious Calculate Function 3: Time Intelligence
- Power BI DAX - the mysterious Calculate function 4 - tricks
- Power BI - Dynamic text for detailed reports 1: text boxes
- Power BI - Advanced options to make forecasting models
- Power BI -Power Query M functions versus DAX
- Maintain a competitive edge by exploiting DAX in Power BI
Infographics
Training manual sample
Below are some extracts from our Power BI DAX manual.
What is DAX (Data Analysis
eXpressions)?
DAX stands for Data Analysis Expressions. It is a formula language used to improve the speed and performance of data when it is being analysed in Power BI Desktop. There are 3 main outputs for creating any DAX formula:
• Measures – which mainly analyse numerical data
• Calculated Tables – where new tables can be created alongside the existing dataset
DAX structure and syntax
DAX formulas have many similarities to Excel formulas. For
example, they they can contain nested functions, conditional statements, and value references However,
there are two main differences to be aware of:
EXCEL |
DAX |
Formula
starts with = |
Formula
starts with ‘Column Name’ = OR ‘Measure Name
= OR ‘Table
Name’ =
|
Formula uses cell references to build calculations
|
Formula
references each row by a field name within a table eg. [Profit] |
To illustrate the use of DAX, here is an example of how a
calculated column can be created based on an existing column within the same
table:
|
Why is
DAX important?
When using Power BI Desktop to analyse the data, you can create many reports that provide the end user with a lot of Business Intelligence without any DAX required. However, an end user may want specific information that is only possible to obtain with DAX. For example, % sales growth for current year compared to previous year broken down by region or the number of days between order date and delivery date. A sound knowledge of DAX will really help the end user to solve many complex problems in order to make informed business decisions.
There are other benefits to using DAX such as:
• When a visual does not allow the end user to fully analyse the data it represents eg. Geo maps not accurately mapping UK cities to their geographical locations
• Being able to re-use a DAX measure numerous times meaning that a calculation used in another calculation will not need to be reprocessed each time because the original calculation will have been stored in memory
• When a visual needs to display numerous measures one measure at a time depending on what is selected in a slicer eg. a line chart displaying either Profit, Sales Costs or Total Extended Price over time
Calculated Columns versus Measures
Calculated Columns and Measures are the main ways to create
DAX formulas and sometimes both methods can be used to get the same result. With
a measure, the calculation has the same syntax and can reference tables,
columns and set conditions just like with a calculated column. Where it differs
is in the way it is stored within the Power BI dataset (or data model) which
has an impact on data performance:
|
CALCULATED
COLUMN |
MEASURE |
Storage |
Physically
resides inside a table |
Has no ‘fixed
abode’ and may be ‘stored’ within a ‘virtual table’ with other DAX measures
|
Performance |
Slow
as the extra column of data takes up memory when data is processed
|
Only
takes up memory if used in a visual on a report so performance is better |
When to use |
When the new
data created is non numerical eg. The ‘New Product Type’ column (as described
earlier) could not be created using a measure |
When datasets
are huge, a measure is preferred over a column calculation |
Whether a measure or calculated column is used depends very
much on the data and how it needs to be manipulated.
Data Model
When using DAX on any dataset, it is important to think
about how the dataset is structured and if there is a Data Model in place. In a
very few cases, all data can come from a single data source where the data
itself is represented as a ‘flat file’ ie. a single table containing all the
data. In reality, the information is more often stored across a number of
different tables where there is a common thread or a relationship between 2
tables. It is important to realise that the success of your DAX calculations
depends on a robust data model where data from different tables can be used in
the same formula. In fact, there are situations where data from different tables
that are not ‘related’ via the Data Model can still be part of a calculation. A
sound working knowledge of the Data Model and how it works is therefore
essential to understand why DAX calculations behave the way they do
Visualisations
The same DAX formula
produces different results because the fields that surround it in a visual are
different. This is what is referred to as ‘Filter context’ and this concept
will be explored later on when looking at specific DAX formulas. To use an
analogy, think of each letter of the alphabet having different sounds depending
on which letters surround it. So too does DAX behave differently within certain
‘filter contexts’
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...