CALCULATED FIELDS

WITH DAX
 MEET
DAX
Data Analysis Expressions, commonly known as DAX, is the formula language that drives
Power BI. With DAX, you can:
• Add calculated columns and measures to your model, using intuitive syntax
• Go beyond the capabilities of traditional “grid-style” formulas, with powerful and flexible
functions
built specifically to work with relational data models

Two ways to use DAX

1) Calculated Columns 2) Measures
 CALCULATED COLUMNS

Calculated columns allow you to add new, formula-based columns to tables

• No “A1-style” references; calculated columns refer to HEY THIS IS IMPORTANT!
entire tables or columns As a rule of thumb, use calculated
columns when you want to “stamp”
• Calculated columns generate values for each row, which static, fixed values to each row in a
are visible within tables in the Data view table (or use the Query Editor!)
DO NOT use calculated columns for
• Calculated columns understand row context; they’re great aggregation formulas, or to calculate
for defining properties based on information in each row, fields for the “Values” area of a
but generally useless for aggregation (SUM, COUNT, etc) visualization (use measures instead)

PRO TIP:
Calculated columns are typically used for filtering data, rather than creating numerical values
 CALCULATED COLUMNS
(EXAMPLES)
In this case we’ve added a calculated column named
“Parent”, which equals “Yes” if the [TotalChildren] field
is greater than 0, and “No” otherwise (just like Excel!)
• Since calculated columns understand row context, a
new value is calculated in each row based on the
value in the [TotalChildren] column
• This is a valid use of calculated columns; it creates a
new row “property” that we can now use to filter or
segment any related data within the model

Here we’re using an aggregation function (SUM) to

calculate a new column named TotalQuantity
• Since calculated columns do not understand filter
context, the same grand total is returned in every
single row of the table
• This is not a valid use of calculated columns;
these values are statically “stamped” onto the
table and can’t be filtered, sliced, subdivided, etc.
 MEASURES

Measures are DAX formulas used to generate new calculated values

• Like calculated columns, measures reference entire tables or
columns (no A1-style or “grid” references)

• Unlike calculated columns, measure values aren’t visible within HEY THIS IS IMPORTANT!
As a rule of thumb, use measures
tables; they can only be “seen” within a visualization like a (vs. calculated columns) when a
chart or matrix (similar to a calculated field in an Excel pivot) single row can’t give you the
answer (in other words, when you
• Measures are evaluated based on filter context, which means need to aggregate)
they recalculate when the fields or filters around them change
(like when new row or column labels are pulled into a matrix or
when new filters are applied to a report)

PRO TIP:
Use measures to create numerical, calculated values that can be analyzed in the “values” field of a report visual
 RECAP: CALCULATED COLUMNS VS.
MEASURES
CALCULATED COLUMNS
MEASURES
• Values are calculated based on information from each Values are calculated based on information from any
• filters in the report (has filter context)
row of a table (has row context)
Does not create new data in the tables themselves
• Appends static values to each row in a table and (doesn’t increase file size)
•
stores them in the model (which increases file size) Recalculate in response to any change to filters within
the report
• Recalculate on data source refresh or when changes
• Almost always used within the values field of a visual
are made to component columns
• Primarily used as rows, columns, slicers or filters
•

Calculated columns “live” in tables Measures “live” in visuals

 ADDING COLUMNS &
MEASURES
Option 1: Select “New Measure” or Option 2: Right-click within the table (in the Data view)
“New Column” from the Home tab* or the Field List (in either the Data or Report view)

When you insert Columns or Measures using the Home tab

(Option 1), they are assigned to whichever table is currently
selected, or the first table in the field list by default
• Measures can be reassigned to new “Home” tables (under the
“Structure” options in the contextual Measure Tools tab), but
Option 2 allows you to be more deliberate about placing them

• NOTE: Assigning measures to specific tables doesn’t have ANY

impact on functionality – it’s just a way to keep them
organized

*In older versions of Power BI, the “New Measure” and “New Column” options were found in the Modeling tab of the Data view
QUICK MEASURES

Quick Measures are pre-built formula

templates that allow you to drag and drop
fields, rather than write DAX from scratch

While these tools can be helpful for defining

more complex measures (like weighted
averages or time intelligence formulas),
they encourage laziness and don’t help you
understand the fundamentals of DAX

PRO TIP:
Just say “NO” to quick measures
(you’re better than that)
 IMPLICIT VS. EXPLICIT
MEASURES

Implicit measures are created when you drag raw numerical fields
(like “OrderQuantity”) into the values pane of a visual and manually
select the aggregation mode (Sum, Average, Min/Max, etc)

Explicit measures are created by actually entering DAX functions

(or
adding “quick measures”) to define calculated columns or measures

HEY THIS IS IMPORTANT!

Implicit measures are only accessible within the specific visualization
in which it was created, and cannot be referenced elsewhere
Explicit measures can be used anywhere in the report, and
referenced
within other DAX calculations to create “measure trees”
Example of an implicit measure
 UNDERSTANDING FILTER CONTEXT

Remember that measures are evaluated based on filter context, which means that they
recalculate whenever the fields or filters around them change

For this particular value in the matrix, the Total Orders measure is calculated based
on the following filter context: Products[ProductName] = “Touring Tire Tube”
• This allows the measure to return the total order quantity for each product specifically (or whatever
the row and column labels dictate – years, countries, product categories, customer names, etc)

This Total is not calculated by summing the values above; it evaluates as its own
measure, with no filter context (since we aren’t calculating orders for a specific
product)

HEY THIS IS IMPORTANT!

Each measure value in a report is like an island, and calculates
according to it’s own filter context (even Totals and Grand Totals)
 FILTER CONTEXT
(EXAMPLES)
MEASURE: Total Revenue MEASURE: Total Orders MEASURE: Total Orders
FILTER CONTEXT: FILTER CONTEXT: FILTER CONTEXT:
• Calendar[Year] = 2016 or 2017 • Calendar[Year] = 2016 or 2017 • Calendar[Year] = 2016 or 2017
• Customers[Full Name] = Mr. Larry Munoz • Customers[Gender] = F (Female) • Customers[Occupation] = Clerical

This is a page-level
filter, which impact
ALL visuals on the
report page

MEASURE: Total Orders MEASURE: Total Orders MEASURE: Total Revenue

FILTER CONTEXT: FILTER CONTEXT: FILTER CONTEXT:
• Calendar[Year] = 2016 or 2017 • Calendar[Year] = 2016 or 2017 • Calendar[Year] = 2016 or 2017
• Calendar[Month] = August 2016
 STEP-BY-STEP MEASURE
CALCULATION
How exactly is this measure calculated?
• REMEMBER: This all happens instantly behind the scenes, every time the filter context changes

STEP 1 STEP 2 STEP 3

Filter context is detected & applied Filters flow “downstream” to Measure formula evaluates
all related tables against the filtered table

Product Table

Accessories

Product[CategoryName] = “Accessories”
= 1,115
Count of rows in the AW_Returns_Data
Product Table table, filtered down to only rows
11 where the product category is
Accessories “Accessories”
AW_Returns_Data
*
* Accessories
AW_Sales_Data

Accessories
COMMON DAXFUNCTIONS
 DAX SYNTAX
MEASURE NAME
Referenced Referenced
• Note: Measures are always surrounded
in brackets (i.e. [Total Quantity]) when TABLE COLUMN NAME
referenced in formulas, so spaces are OK NAME

Total Quantity: =SUM(Transactions[quantity])

Note: This is a “fully qualified” column, since it’s preceeded by the table
FUNCTION NAME name -- table names with spaces must be surrounded by single quotes:
• Calculated columns don’t always use functions, but • Without a space: Transactions[quantity]
measures do: • With a space: ‘Transactions Table’[quantity]
• In a Calculated Column, =Transactions[quantity]
returns the value from the quantity column in
each row (since it evaluates one row at a time)
PRO TIP:
• In a Measure, =Transactions[quantity] will For column references, use the fully qualified name (i.e. Table[Column])
return an error since Power BI doesn’t know how For measure references, just use the measure name (i.e. [Measure])
to translate that as a single value (you need
some sort of aggregation)
 DAX OPERATORS
Arithmetic Comparison
Operator Meaning Example Operator Meaning Example

+ Addition 2+7 = Equal to [City]=“Boston”

- Subtraction 5–3 > Greater than [Quantity]>10

* Multiplication 2*6 < Less than [Quantity]<10

/ Division 4/2 >= Greater than or equal to [Unit_Price]>=2.5

^ Exponent 2^5 <= Less than or equal to [Unit_Price]<=2.5

<> Not equal to [Country]<>”Mexico”

Pay attention to these!

Text/Logical Operator Meaning Example

& Concatenates two values to produce one text string [City] & “ “ & [State]

&& Create an AND condition between two logical expressions ([State]=“MA”) && ([Quantity]>10)

|| (double pipe) Create an OR condition between two logical expressions ([State]=“MA”) || ([State]=“CT”)

IN Creates a logical OR condition based on a given list (using curly brackets) ‘Store Lookup’[State] IN { “MA”, “CT”, “NY” }

*Head to [Link] for more information about DAX syntax, operators, troubleshooting, etc
 COMMON FUNCTION CATEGORIES

MATH & STATS LOGICAL TEXT FILTER DATE & TIME

Functions Functions Functions Functions Functions
Basic aggregation Functions for returning Functions to manipulate Lookup functions based Basic date and time
functions as well as information about values text strings or control on related tables and functions as well as
“iterators” evaluated at in a given conditional formats for dates, times filtering functions for advanced time
the row-level expression or numbers dynamic calculations intelligence operations

Common Examples: Common Examples: Common Examples: Common Examples: Common Examples:
• SUM • IF • CONCATENATE • CALCULATE • DATEDIFF
• AVERAGE • IFERROR • FORMAT • FILTER • YEARFRAC
• MAX/MIN • AND • LEFT/MID/RIGHT • ALL • YEAR/MONTH/DAY
• DIVIDE • OR • UPPER/LOWER • ALLEXCEPT • HOUR/MINUTE/SECOND
• COUNT/COUNTA • NOT • PROPER • RELATED • TODAY/NOW
• COUNTROWS • SWITCH • LEN • RELATEDTABLE • WEEKDAY/WEEKNUM
• DISTINCTCOUNT • TRUE • SEARCH/FIND • DISTINCT
• FALSE • REPLACE • VALUES Time Intelligence Functions:
Iterator Functions: • REPT • EARLIER/EARLIEST • DATESYTD
• SUBSTITUTE • HASONEVALUE • DATESQTD
• SUMX
• TRIM • HASONEFILTER • DATESMTD
• AVERAGEX
• UNICHAR • ISFILTERED • DATEADD
• MAXX/MINX
• USERELATIONSHIP • DATESINPERIOD
• RANKX
• COUNTX

*Note: This is NOT a comprehensive list (does not include trigonometry functions, parent/child functions, information functions, or other less common functions)
 BASIC DATE & TIME
FUNCTIONS
DAY/ Returns the day of the month (1-31), month
=DAY/MONTH/
MONTH/ of the year (1-12), or year of a given date

YEAR() YEAR(Date)
HOUR/MINUTE/ Returns the hour (0-23), minute (0-59), or
second (0-59) of a given datetime value =HOUR/MINUTE/SECOND(Datetime)
SECOND()

TODAY/NOW() Returns the current date or exact time =TODAY/NOW()

WEEKDAY/ Returns a weekday number from 1 (Sunday)

to 7 (Saturday), or the week # of the year =WEEKDAY/WEEKNUM(Date, [ReturnType])
WEEKNUM()
Returns the date of the last day of the
EOMONTH() month, +/- a specified number of months =EOMONTH(StartDate, Months)

Returns the difference between two dates,

DATEDIFF( based on a selected interval =DATEDIFF(Date1, Date2, Interval)
)
 BASIC LOGICAL FUNCTIONS
(IF/AND/OR)
Checks if a given condition is met, and
IF() returns one value if the condition is TRUE, =IF(LogicalTest, ResultIfTrue, [ResultIfFalse])
and another if the condition is FALSE

Evaluates an expression and returns a

IFERROR() specified value if the expression returns an =IFERROR(Value, ValueIfError)
error, otherwise returns the expression itself

Checks whether both arguments are TRUE,

AND() and returns TRUE if both arguments are =AND(Logical1, Logical2) Note: Use the && and
TRUE, otherwise returns FALSE
|| operators if you
want to include more
Checks whether one of the arguments is than two conditions!
OR() TRUE to return TRUE, and returns FALSE if =OR(Logical1, Logical2)
both arguments are FALSE
 TEXT FUNCTIONS
Note: Use the & operator as a shortcut,
LEN() Returns the number of characters in a string =LEN(Text or to combine more than two strings!

)
CONCATENATE() Joins two text strings into one =CONCATENATE(Text1, Text2)

LEFT/ Returns a number of characters from the =LEFT/RIGHT(Text, [NumChars])

MID/ start/middle/end of a text string
=MID(Text, StartPosition, NumChars)
RIGHT()
UPPER/LOWER/ Converts letters in a string to
PROPER() upper/lower/proper case =UPPER/LOWER/PROPER(Text)

Replaces an instance of existing text with =SUBSTITUTE(Text, OldText, NewText,

SUBSTITUTE() new text in a string
[InstanceNumber])

Returns the position where a specified string =SEARCH(FindText, WithinText,

SEARCH() or character is found, reading left to right
[StartPosition], [NotFoundValue])
 RELATED

RELATED() Returns related values in each row of a table based on relationships with other tables

=RELATED(ColumnName)
HEY THIS IS IMPORTANT!
RELATED works almost exactly like a VLOOKUP function – it uses
The column that contains the the relationship between tables (defined by primary and foreign
values you want to retrieve keys) to pull values from one table into a new column of another
Since this function requires row context, it can only be used as a
Examples:
• Product_Lookup[ProductName] calculated column or as part of an iterator function that cycles
• Territory_Lookup[Country] through all rows in a table (FILTER, SUMX, MAXX, etc)

PRO TIP:
Avoid using RELATED to create redundant calculated columns unless you absolutely need them, since those
extra columns increase file size; instead, use RELATED within a measure like FILTER or SUMX
 BASIC MATH & STATS FUNCTIONS

SUM() Evaluates the sum of a column =SUM(ColumnName)

Returns the average (arithmetic

AVERAGE() mean) of all the numbers in a column
=AVERAGE(ColumnName)

Returns the largest value in a column

MAX() or between two scalar expressions
=MAX(ColumnName) or =MAX(Scalar1, [Scalar2])

Returns the smallest value in a column

MIN() or between two scalar expressions =MIN(ColumnName) or =MIN(Scalar1, [Scalar2])

Performs division and returns the

DIVIDE() alternate result (or blank) if
=DIVIDE(Numerator, Denominator, [AlternateResult])
div/0
 COUNT, COUNTA, DISTINCTCOUNT & COUNTROWS

Counts the number of cells in a column that

COUNT() contain numbers
=COUNT(ColumnName)

Counts the number of non-empty cells in a

COUNTA( column (numerical and non-numerical)
=COUNTA(ColumnName)
)
Counts the number of distinct or unique
DISTINCTCOUNT() values in a column
=DISTINCTCOUNT(ColumnName)

Counts the number of rows in the specified

COUNTROWS() table, or a table defined by an expression
=COUNTROWS(Table)
 CALCULATE

CALCULATE() Evaluates a given expression or formula under a set of defined filters

=CALCULATE(Expression, [Filter1], [Filter2],…)

Name of an existing measure, or a DAX List of simple Boolean (True/False) filter expressions
formula for a valid measure (note: these require simple, fixed values; you cannot
Examples:
create filters based on measures)
• [Total Orders] Examples:
• SUM(Returns_Data[ReturnQuantity]) • Territory_Lookup[Country] = “USA”
• Calendar[Year] > 1998

PRO TIP:
CALCULATE works just like SUMIF or COUNTIF in Excel, except it can evaluate measures based on ANY
sort of calculation (not just a sum, count, etc); it may help to think of it like “CALCULATEIF”
 CALCULATE
(EXAMPLE)

Here we’ve defined a new measure named “Bike Returns”, which

evaluates the “Total Returns” measure when the CategoryName
in the Products table equals “Bikes”

HEY THIS IS IMPORTANT!

Wait, why do we see the same repeating values when
CALCULATE modifies and overrules any competing filter context!
we view a matrix with different categories on rows?
In this example, the “Clothing” row has filter context of
Shouldn’t these cells have different filter contexts for CategoryName = “Clothing” (defined by the row label) and
Accessories, Clothing, Components, etc? CategoryName= “Bikes” (defined by the CALCULATE function)
Both cannot be true at the same time, so the “Clothing” filter is
overwritten and the “Bikes” filter (from CALCULATE) takes priority
 CALCULATE CHANGES THE FILTER
CONTEXT
CALCULATE If the measure being evaluated contains a CALCULATE
Filters modified by CALCULATE function, filter context is overwritten between Step 1 & Step 2
[CategoryName] = “Bikes”

STEP 1 STEP 2 STEP 3

Filter context is Filters flow “downstream” to Measure formula evaluates
detected & applied all related tables against the filtered table
Product Table

Bikes Product Table

Bikes

Count of rows in the AW_Returns_Data

Products[CategoryName] = “Accessories” 11
table, filtered down to only rows
where the product category is “Bikes” = 342
Product Table AW_Returns_Data
*
Accessories * Bikes
AW_Sales_Data

Bikes
 ALL

ALL() Returns all rows in a table, or all values in a column, ignoring any filters that have been applied

=ALL(Table or ColumnName, [ColumnName1], [ColumnName2],

…)
The table or column that you List of columns that you want to clear filters on (optional)
want to clear filters on Notes:
Examples: • If your first parameter is a table, you can’t specify additional columns
• Transactions • All columns must include the table name, and come from the same table
• Products[ProductCategory]
Examples:
• Customer_Lookup[CustomerCity], Customer_Lookup[CustomerCountry]
• Products[ProductName]

PRO TIP:
Instead of adding filter context, ALL removes it. This is often used when you need unfiltered values that
won’t react to changes in filter context (i.e. % of Total, where the denominator needs to remain fixed)
 FILTE
R
FILTER() Returns a table that represents a subset of another table or expression

=FILTER(Table, FilterExpression) HEY THIS IS IMPORTANT!

FILTER is used to add new filter context,
and can handle more complex filter
Table to be filtered A Boolean (True/False) filter expression
expressions than CALCULATE (by
Examples:
to be evaluated for each row of the table referencing measures, for example)
• Territory_Lookup Examples:
• Customer_Lookup Since FILTER returns an entire table, it’s
• Territory_Lookup[Country] = “USA”
almost always used as an input to other
• Calendar[Year] = 1998
• Products[Price] > [Overall Avg Price]
functions, like CALCULATE or SUMX

PRO TIP:
Since FILTER iterates through each row in a table, it can be slow and processor-intensive; don’t use FILTER
if a CALCULATE function will accomplish the same thing
 ITERATOR (“X”) FUNCTIONS
Iterator (or “X”) functions allow you to loop through the same calculation or expression on
each row of a table, and then apply some sort of aggregation to the results (SUM, MAX, etc)

=SUMX(Table, Expression)

Aggregation to apply Table in which the Expression to be evaluated for

to calculated rows* expression will be evaluated each row of the given table
Examples: Examples: Examples:
• SUMX • Sales • [Total Orders]
• COUNTX • FILTER(Sales, • Sales[RetailPrice] * Sales[Quantity]
• AVERAGEX RELATED(Products[Category])=“Clothing”)
• RANKX
• MAXX/MINX

PRO TIP:
Imagine the function adding a temporary new column to the table, calculating the value in each row
(based on the expression) and then applying the aggregation to that new column (like SUMPRODUCT)

*In this example we’re looking at SUMX, but other “X” functions follow a similar syntax
 TIME INTELLIGENCE FORMULAS

Time Intelligence functions allow you to easily calculate common time comparisons:

Performance =CALCULATE(Measure, DATESYTD(Calendar[Date]))

To-Date Use DATESQTD for Quarters or DATESMTD for Months

Previous =CALCULATE(Measure, DATEADD(Calendar[Date], -1, MONTH))

Period
Select an interval (DAY, MONTH, QUARTER, or YEAR) and the
# of intervals to compare (i.e. previous month, rolling 10-day)
Running =CALCULATE(Measure,
Total DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -10, DAY))

PRO TIP:
To calculate a moving average, use the running total calculation above and divide by the number of intervals
BEST PRACTICES: CALCULATED COLUMNS &
MEASURES
Don’t use a calculated column when a measure will do the trick
• Only use calculated columns to “stamp” static, fixed values to each row in a table
• Use measures when aggregation is necessary, or to create dynamic values in a report

Write measures for even the simplest calculations (i.e. Sum of Sales)
• Once you create a measure it can be used anywhere in the report and as an input to other,
more complex calculations (no implicit measures!)

Break measures down into simple, component parts

• DAX is a difficult language to master; focus on practicing and understanding simple
components at first, then assemble them into more advanced formulas

Reference columns with the table name, and measures alone

• Using “fully qualified” column references (preceeded by the table name) helps make formulas
more readable and intuitive, and differentiates them from measure references
 BEST PRACTICES: SPEED &
PERFORMANCE
Eliminate redundant columns; keep data tables narrow
• Data tables should ideally only contain only quantitative values and foreign keys; any extra descriptive
columns can usually live in a related lookup table

Imported columns are better than calculated columns

• When possible, create calculated columns at the source (i.e. in your raw database) or within the
Query Editor; this is more efficient than processing those calculations in the Data Model

Minimize iterator functions (FILTER, SUMX, etc.)

• Functions that cycle through each row in a table are “expensive”, meaning that they take
time and consume processing power
 Reference sources:

- Microsoft PowerBI website

- PowerBI resources on Coursera, Udemy
 Disclaimer
The information in this document is highly confidential and may be legally privileged. It
is intended solely for the addressee. Access to this presentation by anyone else is
unauthorized. If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited and may be
unlawful. The sample screens shown in this presentation are CONVZ FZE’s IP and
cannot be used or distributed without their prior consent. This presentation is
considered approved for submission to the Client by the Above-Authorized signatory.