DAX for creating
tables and columns
DA X I N P OW E R B I
Carl Rosseel
Curriculum Manager at DataCamp
DAX stands for data analysis expressions
DAX is a formula expression language used in multiple Microso analytics tools
DAX formulas include functions, operators and values to perform advanced calculations
DAX formulas are used in:
Measures
Calculated columns
Calculated tables
Row-level security
DAX IN POWER BI
The power of DAX
It opens up new capabilities:
Joins, lters, measures and calculated elds become part of your toolbox
DAX + Power Query = a powerful data analysis tool:
Dive deeper into the data and extract key insights
Use DAX for rapid prototyping
DAX IN POWER BI
Measures vs calculated columns
Calculated Columns:
Calculated on data import
Visible in data & report Pane
COST = Orders[Sales] - Orders[Profit]
Order_ID Sales Po t Cost
3151 $77.88 $3.89 $73.99
3152 $6.63 $1.79 $4.84
3153 $22.72 $10.22 $12.50
3154 45.36 $21.77 $23.59
DAX IN POWER BI
Measures vs calculated columns
Calculated Columns: Measures:
Calculated on data import Calculated at query run-time
Visible in data & report Pane Visible only in report pane
COST = Orders[Sales] - Orders[Profit] Total Sales = SUM(Orders[Sales])
Order_ID Sales Po t Cost Region Total Sales
3151 $77.88 $3.89 $73.99 Central $501,239.89
3152 $6.63 $1.79 $4.84 East $678,781.24
3153 $22.72 $10.22 $12.50 West $391,721.91
3154 45.36 $21.77 $23.59 South $725.457.82
Total $2,297,200.86
DAX IN POWER BI
Context allows you to perform dynamic analysis
There are three types of context: row, query and lter context
Row context: (1)
"The current row"
DAX calculated columns
COST = Orders[Sales] - Orders[Profit]
DAX IN POWER BI
Context allows you to perform dynamic analysis
There are three types of context: row, query and lter context
Row context: (1)
"The current row"
DAX calculated columns
COST = Orders[Sales] - Orders[Profit]
Order_ID Sales Po t Cost
3151 $77.88 $3.89 $73.99
3152 $6.63 $1.79 $4.84
3153 $22.72 $10.22 $12.50
3154 45.36 $21.77 $23.59
DAX IN POWER BI
Context allows you to perform dynamic analysis
There are three types of context: row, query and lter context
Query context: (2)
Refers to the subset of data that is implicitly retrieved for a formula
Controlled by slicers, page lters, table columns and row headers
Controlled by chart/visual lters
Applies a er row context
DAX IN POWER BI
Context allows you to perform dynamic analysis
Query context: (2) Query context: (2)
Example: Filter data by Region. Example: Filter data by State.
Region Total Sales State Total Sales
Central $501,239 Alabama $13,724
East $678,781 Arizona $38,710
West $391,721 Arkansas $7,669
South $725.457 California $381,306
DAX IN POWER BI
Context allows you to perform dynamic analysis
There are three types of context: row, query and lter context
Filter Context: (3)
The set of values allowed in each column, or in the values retrieved from a related table
By using arguments to a formula or by using report lters on row and column headings
Applies a er query context
DAX IN POWER BI
Context allows you to perform dynamic analysis
There are three types of context: row, query and lter context.
Filter Context (3)
Total Costs East = CALCULATE([Total Costs], Orders[Region] = 'East')
Region Total costs Total costs East
Central $617,039
East $587,258 $587,258
West $461,534
South $344,972
Total $2,010,804 $587,258
DAX IN POWER BI
Context in a nutshell
DAX IN POWER BI
World wide importers dataset
A ctitious wholesaler who imports and
distributes novelty goods
The dataset consists of:
A fact table that detailing sales
transactions
Multiple other dimension tables:
Dates
Customers
Cities
Employees
Stock Items
DAX IN POWER BI
Let's practice!
DA X I N P OW E R B I
DAX for calculated
tables and columns
DA X I N P OW E R B I
Carl Rosseel
Curriculum Manager
Let's practice!
DA X I N P OW E R B I
Methods to create
DAX measures
DA X I N P OW E R B I
Carl Rosseel
Curriculum Manager
Implicit vs explicit measures
Implicit Explicit
Automatically created by Power BI Writing measures in an explicit way
Comes directly from the Database E.g.: Total Sales = SUM(Orders[Sales])
E.g.: If we drag Sales to values of a table, O er exibility
Power BI will automatically sum it
Using a dropdown menu we can de ne the
aggregation: sum, average, count, ...
DAX IN POWER BI
Why explicit measures are preferred
Reduces confusion of what a measure is or does
Total Sales = SUM(Orders[Sales])
Total Sales is more clear than Sales (SUM, AVG, MIN, ... ?)
Reusable within other measures
Total Sales East = CALCULATE([Total Sales],Orders[Region] = 'East')
Can be given a custom name to explain its functionality
Makes maintenance of complex models more sustainable
DAX IN POWER BI
Best practices
Keep DAX measures grouped together: Format and comment with DAX:
Measures are free to move to any table Use indentations to increase
understanding
This is in contrast with calculated
Shi Enter to start a new line
columns, which belong to a speci c table
Tab to indent
Add comments a er a //
DAX IN POWER BI
Use variables to improve your formulas
Stores the result of an expression as a Syntax:
named variable
VAR <name> = <expression>
Can be used as an argument to other
Name = The name of the variable
measure expressions
A DAX expression which returns a scalar
Four main advantages:
or table value
Improve performance
Followed by a RETURN statement
Improve readability
Simplify debugging
Reduce complexity
DAX IN POWER BI
Use variables to improve your formulas - example
Calculate the sales from last year and store it as a variable
VAR
SALESPRIORYEAR = CALCULATE([SALES],SAMEPERIODLASTYEAR('DATE'))
RETURN
Use the variable in a formula
Sales growth = [Sales] - SALESPRIORYEAR
DAX IN POWER BI
Use variables to improve your formulas - example
All together it would look like this:
Sales growth =
VAR
SALESPRIORYEAR = CALCULATE([SALES],SAMEPERIODLASTYEAR('DATE'))
RETURN
Sales growth = [Sales] - SALESPRIORYEAR
DAX IN POWER BI
Let's practice!
DA X I N P OW E R B I
DAX and measures
DA X I N P OW E R B I
Carl Rosseel
Curriculum Manager
Let's practice!
DA X I N P OW E R B I
