08 – Implementing

Calculations In DAX
Chris Webb
[email protected]
Agenda
• Common calculations in calculated columns
• Aggregating the result of expressions in measures
• The Calculate() function
• Writing percentage share calculations
• Calculations that force selections
• Ranks
• Using variables
• Creating a Date table
• Implementing Date calculations
Calculated Columns
• Expressions in calculated columns can reference any value in the
same row in the same table
• This is the concept of row context
• To reference values in other tables you need to use the RELATED() and
RELATEDTABLE() functions
Simple Aggregate Functions
• COUNTROWS() returns the number of rows in a table
• SUM() returns the sum of a column of numeric values
• MIN(), MAX() and AVERAGE() all do what you’d expect
• DISTINCTCOUNT() returns the number of distinct values in a column
• These functions are generally used inside measures, eg
Sales Amount=SUM(Sales[SalesAmount])
Aggregating Expressions In Measures
• The simple aggregation functions like SUM() only work on columns
• To aggregate the result of an expression, use functions like SUMX()
• The expression you’re aggregating knows about row context
• You always have to supply a table to aggregate over to provide this context
• For example:
Sales After Discount=
SUMX(Sales, Sales[SalesAmount] * .8)
• This does not have the memory overhead of creating a calculated
column and then creating a measure on top
Filter Context
• Filter context refers to the currently selected item on each column on
each table
• Remember that more than one item can be selected, so the selection is
represented as a table
• Think of it like the filters you can apply to tables in Excel
• Filter context filters the rows in your table, and the result of this filter
is the table over which values are aggregated for measures
• Filter context can travel along relationships
Calculate()
• The CALCULATE() function is the key to all advanced DAX calculation
functionality
• Signature: Calculate(Expression, SetFilter1, SetFilter2,...)
• It allows an expression returning a value to be evaluated in an altered
filter context
• It works as follows:
• Modifies the current filter context according to the SetFilter arguments you
pass in
• Shifts the row context onto the filter context
• Evaluates your expression in the new filter context
Changing The Filter Context
• There are several ways the SetFilter arguments can be used to change
the filter context
• Boolean expressions ignore the original filter context and set a new one
• Table expressions take the original filter context and filter it further, or
overwrite it
• The USERELATIONSHIP() function makes an inactive relationship the active
relationship
• The CROSSFILTER() function does everything that USERELATIONSHIP() does
plus allows you to set filter direction
All(), AllExcept() And AllSelected()
• The ALL() function returns either:
• A table with all filter context removed
• A table of all the distinct values from one column ignoring filter context
• ALLEXCEPT() is similar to ALL() but on all columns in a table except
those you specify
• ALLSELECTED() returns a table with filter context for rows and
columns alone removed
Percentage Share Calculations
• Percentage share calculations involve dividing
• A measure value by
• The value for that measure at a subtotal or grand total level
• You can use a combination of CALCULATE() and
ALL()/ALLEXCEPT()/ALLSELECTED() to get the subtotal or grand total
• The DIVIDE() function should be used for all divisions because it
avoids division-by-zero errors
Values(), Filter() And Forcing Selections
• The VALUES() function returns a table containing all of the distinct
values in a column in the current filter context
• The FILTER() function takes a table and then filters the rows in it
• You can combine these functions with CALCULATE() to create
measures that force a selection of some value
• For example, if you have a measure that shows Sales, you could create
another measure that shows Sales in Australia
Ranks
• The RANKX() function can be used to calculate rank values
• The first parameter is a table to rank over – usually provided by the
ALL() or ALLSELECTED() functions
• The second parameter is the value used to calculate the rank – usually
another measure
DAX Variables
• DAX variables allow you to split a measure definition up into steps
• Each step can return a value that is
• A table, or
• A single value
• Syntax:
MyMeasure =
VAR FirstVariable = 1
VAR SecondVariable = 2
RETURN FirstVariable + SecondVariable
Building A Date Table
• It is essential that you use a Date table in your Data Model if you intend
to do date-based calculations
• If you don’t, you may find:
• Some functions throw errors
• Some calculations don’t return the values you expect
• You are unable to handle special situations such as bank holidays
• There are many ways to generate a Date table:
• Import from the DateStream dataset in Azure DataMarket
• Generate your own using SQL
• Generate your own in M in a query
• Create a table in Excel and import that
Date Table Requirements
• A column of data type Date
• This column to be used as the destination for any relationships
• No missing dates in your date range
• Complete years:
• Start at the beginning of the year of your earliest date
• End at the end of the year of your latest date
• Create columns for months, quarters, years, financial periods etc
Automatic Date Table Creation
• Power BI will, by default, create a hidden Date table for each date or
date/time type column in the model
• You can stop this happening in the Options dialog
• Probably not a good idea to use this in most cases:
• Confusing – when you drag a date field onto a report, you see a hierarchy
with no dates in it instead!
• Should you be using a regular calendar hierarchy? Not as flexible as a home-
made date table
• You can’t use them in calculations
• Will increase memory usage
Time Intelligence Calculations
• The basic technique for most time intelligence calculations is:
• Use an aggregate function like SUM() on a column
• Place this aggregate function inside a CALCULATE()
• Then use the filter parameters of CALCULATE() to shift the context based on
what is already selected on the Date table
• There are many short-cut functions that make building these
calculations easier, but they are all based on CALCULATE()
• Always use the Date column from your Date table in calculations – do
not use a column from your fact table!
Multiple Date Tables
• Often you will need many Date tables in a model
• For example, you may want to analyse by Order Date and Ship Date
• You can import the Date table once then used calculated tables to
duplicate it
• Some good practices:
• Be careful with naming conventions so that users do not get confused
• Use inactive relationships appropriately
Semi-Additive Measures
• Semi-additive measures are measures that aggregate normally along all
dimensions except time
• Examples include:
• Number of units in stock in a warehouse
• Balance of a bank account
• Many options for special aggregation by time:
• Find the value of the last/first date in the current time period
• Find the value of the last/first non-empty date in the current time period
• Find the value of the last non-empty date going back from the current time
period to the beginning of time
• Find the daily average over all dates in the current time period
Closing Balance
• The most common semi-additive measure is the closing balance
• For example, when looking at the balance of a bank account for a
year, you might actually want to show the balance of the bank
account on the last day of the year
• Summing up bank balances for the whole year would make no sense
• The solution here is to use CALCULATE() to narrow the filter context to
the last date in the current selection
Closing Balance With LastDate()
• The LASTDATE() function returns the last date in the current context
Last Date=LASTDATE(DateTable[DateColumn])
• Requires a column of type Date to work with
• It can be interpreted as either a single value or a table containing a
single value
• This means it can be used as a filter parameter for Calculate()
Last Day Sales=
CALCULATE(SUM(Sales[SalesAmount])
, LASTDATE(DateTable[DateColumn]))
Closing Balance With LastNonBlank()
• To get the value of the last date with values, you need the
LASTNONBLANK() function
• This returns the last value in a column where a particular expression is
not blank
• Therefore, we can use it to find the last date where there are rows in
the fact table, and use it with CALCULATE()
Last Date With Sales:=
LASTNONBLANK(DateTable[DateColumns]
, COUNTROWS(RELATEDTABLE(Sales)))
Running Totals
• Running totals aggregate a measure over a particular time period,
giving a total to date
• From the beginning of time
• Or from a particular starting point such as the beginning of the year
• Again, the basic approach is to use CALCULATE() to modify the filter
context on the Date table so that it is expanded to the desired time
range
• For example, for a total to date, take the last date in the current
selection and derive the list of dates from the beginning of time to
that date
Total To Date With DatesBetween()
• The DATESBETWEEN() function returns a table of dates from one date
to another
• Again, requires a column of type Date
• When the start date parameter is BLANK(), this means that the table
of dates will start with the first ever date
• When the last date parameter is BLANK(), this means that the table of
dates will end with the last ever date
DATESBETWEEN(DateTable[DateColumn]
, BLANK(), LASTDATE(DateTable[DateColumn]))
Total To Date With DatesBetween()
• Since DATESBETWEEN() returns a table, it can be used as a filter
parameter for CALCULATE()
Total Sales To Date=
CALCULATE(
SUM(Sales[SalesAmount])
, DATESBETWEEN(DateTable[DateColumn]
, BLANK(), LASTDATE(DateTable[DateColumn])))
Year To Date
• Year to Date values can be calculated in the same way, but instead of
starting from the beginning of time we need to find the beginning of the
current year
• There is a function DATESYTD() that will return this table, which can be used
with CALCULATE()
• Even easier, there is a TOTALYTD() function that will do the same thing as
CALCULATE() used with DATESYTD()
• Also supports financial years by allowing you to specify a year-end date
YTD Sales=
CALCULATE(SUM(Sales[SalesAmount])
, DATESYTD(DateTable[DateColumn]))
Previous Period Calculations
• Another common type of time intelligence calculation is the ‘previous
period growth’
• They compare a value for the current time period with the same value
for a previous time period, such as the previous day or year
• Again, the approach is to use CALCULATE() and shift the filter context
back to a previous time period relative to the current time period
Previous Period Calculations With DateAdd()
• The DATEADD() function takes a table of dates and shifts them
forward or backwards in time
• Can move dates forward in increments of day, month, quarter or year
• Once again, DATEADD() can be used with CALCULATE() to return a
measure value for the previous time period
Previous Day Sales=
IF(HASONEVALUE(DateTable[DateColumn])
, CALCULATE(SUM(Sales[SalesAmount]),
DATEADD(DateTable[DateColumn], -1, DAY))
, BLANK())
Other Date-Shifting Functions
• SAMEPERIODLASTYEAR() shifts dates back one year
• Useful for Year-on-Year growth calculations
• PARALLELPERIOD() returns a table of dates for the entire previous
year
• NEXTYEAR(), PREVIOUSYEAR() and related functions return a table
containing dates for the entire next/previous year (or quarter or
month etc)
• STARTOFYEAR(), ENDOFYEAR() and related functions return a the date
at the start and end of the current year (or quarter or month etc)