Calculation Basics

Advanced Calculations
DAX to the MAXX()

Paul Turley • Introduction to DAX • DAX function roadmap

• Essential DAX functions • CALCULATE & FILTER functions
Data Platform MVP,
BI Consulting Architect • Calculated columns • How DAX Processes & Calculates Results
Intelligent Business LLC • Measures • Dependent measures
• Aggregators • Evolution of a measure
• Time intelligence • Modifying Row & Filter context
• When to use iterators
• DAX Studio
© 2017 Intelligent Business LLC, intelligentbiz.net
Design Phases
Get Data Transform Model Visualize & Publish &
Analyze Share
Connect Shape Relate Filter Pin
Import Cleanse Calculate Slice Configure
Refresh Rename Hide Interact Collaborate
Stream Merge Format Drill Embed
Filter

DAX

© 2017 Intelligent Business LLC, intelligentbiz.net 2

What is DAX and Where Did it Come From?
1. Expression language, used to perform calculations in:
Power Pivot
Power BI
SQL Server Analysis Service (SSAS) Tabular
2. Query language
3. Language elements derived from:

Excel
functions
SQL DAX
MDX
© 2017 Intelligent Business LLC, intelligentbiz.net 3
Learning DAX ABS
ACOS
DATESMTD
DATESQTD
LEFT
LEN
REPT
RIGHT
ACOSH DATESYTD LN ROLLUP
ACOT DATEVALUE LOG ROLLUPADDISSUBTOTAL
ACOTH DAY LOG10 ROLLUPGROUP
ADDCOLUMNS DEGREES LOOKUPVALUE ROLLUPISSUBTOTAL
ADDMISSINGITEMS DISTINCT LOWER ROUND
ALL DISTINCTCOUNT MAX ROUNDDOWN
ALLEXCEPT DIVIDE MAXA ROUNDUP
ALLNOBLANKROW EARLIER MAXX ROW
ALLSELECTED EARLIEST MEDIAN SAMEPERIODLASTYEAR
AND EDATE MEDIANX SAMPLE
ASIN ENDOFMONTH MID SEARCH
ASINH ENDOFQUARTER MIN SECOND
ATAN ENDOFYEAR MINA SELECTCOLUMNS
ATANH EOMONTH MINUTE SIGN
AVERAGE EVEN MINX SIN
AVERAGEA EXACT MOD SINH
AVERAGEX EXCEPT MONTH SQRT
BETA.DIST EXP MROUND SQRTPI
BETA.INV EXPON.DIST NATURALINNERJOIN STARTOFMONTH
BLANK FACT NATURALLEFTOUTERJOIN STARTOFQUARTER
CALCULATE FILTER NEXTDAY STARTOFYEAR
CALCULATETABLE FILTERS NEXTMONTH STDEV.P
CALENDAR FIND NEXTQUARTER STDEV.S
CALENDARAUTO FIRSTDATE NEXTYEAR STDEVX.P
CEILING FIRSTNONBLANK NOT STDEVX.S
CHISQ.DIST FIXED NOW SUBSTITUTE
CHISQ.DIST.RT FLOOR ODD SUBSTITUTEWITHINDEX
CHISQ.INV FORMAT OPENINGBALANCEMONTH SUM
CHISQ.INV.RT GCD OPENINGBALANCEQUARTER SUMMARIZE
CLOSINGBALANCEMONTH GENERATE OPENINGBALANCEYEAR SUMMARIZECOLUMNS
CLOSINGBALANCEQUARTER GENERATEALL OR SUMX
CLOSINGBALANCEYEAR GEOMEAN PARALLELPERIOD SWITCH

• Understanding essential concepts is COMBIN

COMBINA
CONCATENATE
GEOMEANX
GROUPBY
HASONEFILTER
PATH
PATHCONTAINS
PATHITEM
TAN
TANH
TIME

more important than memorizing CONCATENATEX

CONFIDENCE.NORM
CONFIDENCE.T
HASONEVALUE
HOUR
IF
PATHITEMREVERSE
PATHLENGTH
PERCENTILE.EXC
TIMEVALUE
TODAY
TOPN

functions CONTAINS
COS
COSH
IFERROR
IGNORE
INT
PERCENTILE.INC
PERCENTILEX.EXC
PERCENTILEX.INC
TOTALMTD
TOTALQTD
TOTALYTD
COT INTERSECT PERMUT TRIM

• You can always lookup function syntax COTH

COUNT
COUNTA
ISBLANK
ISCROSSFILTERED
ISEMPTY
PI
POISSON.DIST
POWER
TRUNC
UNICODE
UNION
COUNTAX ISERROR PREVIOUSDAY UPPER

• Keeping a library of working examples COUNTBLANK

COUNTROWS
COUNTX
ISEVEN
ISFILTERED
ISLOGICAL
PREVIOUSMONTH
PREVIOUSQUARTER
PREVIOUSYEAR
USERELATIONSHIP
USERNAME
VALUE

may be more valuable than a web search CROSSFILTER

CROSSJOIN
CURRENCY
ISNONTEXT
ISNUMBER
ISO.CEILING
PRODUCT
PRODUCTX
QUOTIENT
VALUES
VAR.P
VAR.S
CURRENTGROUP ISODD RADIANS VARX.P
CUSTOMDATA ISONORAFTER RAND VARX.S
DATATABLE ISSUBTOTAL RANDBETWEEN WEEKDAY
DATE ISTEXT RANK.EQ WEEKNUM
DATEADD KEEPFILTERS RANKX XIRR
DATEDIFF LASTDATE RELATED XNPV
© 2017 Intelligent Business LLC, intelligentbiz.net DATESBETWEEN
DATESINPERIOD
LASTNONBLANK
LCM
RELATEDTABLE
REPLACE
YEAR
YEARFRAC 4
DAX Learning Curve

#%@!

Elegantly complex

Deceptively simple

Most calculations
© 2017 Intelligent Business LLC, intelligentbiz.net 5
What to Learn
Examples of some important DAX functions & concepts
Beyond the basics, specific functions are useful in different business scenarios
There or than 250 DAX functions and more added with each major release

PERCENTILE() ISERROR() RANKX() RELATEDTABLE()

ALL()
CALCULATETABLE()
IF() SUM() ALLSELECTED()
EARLIER()

CONTAINS()
SWITCH() SUMX()
ISBLANK()
DATEDIFF()
MEASURES
EVALUATE

ROW() DATE() TIME() VALUES()

CALCULATED COLUMNS NOW() DATESINPERIOD()

CALCULATE() HASONEVALUE()
EOMONTH()

DIVIDE()
FILTER() SUMMARIZE()
PERVIOUSMONTH()
TODAY()
FIRSTNONBLANK() TOTALMTD()
SAMEPERIODLASTYEAR() DATESMTD()
© 2017 Intelligent Business LLC, intelligentbiz.net 6
Important Concepts

• Row context • Calculated columns

• Filter context • Measures
• Filter propagation
• Aggregators
• Iterators

© 2017 Intelligent Business LLC, intelligentbiz.net 7

Context
Webster:
“The interrelated conditions in which something exists or occurs”
Turley:
“Where the heck am I?”…
…”and how did I get here?”
Row Context:
“What row am I on?”
Filter Context:
“What filters are applied?”
-1st Class, Bus. Class or Economy
-Window, isle or middle seat

© 2017 Intelligent Business LLC, intelligentbiz.net 8

 Implicit & Explicit Measures
No one right answer Excel doesn’t support Power BI Implicit measures
Two schools of thought: Power BI Report
1. Power BI should behave like Excel Implicit measure
• Excel uses implicit measures with workbook (numeric column)
data
• PivotTables & charts:
• SUM numbers by default
• COUNT text by default
• Power BI Desktop: numeric columns have
Summarize By property No Implicit
Excel Pivot Table
2. All measures should be explicitly
measure

defined
• Model designer maintains control
• Default behavior may not always be right

© 2017 Intelligent Business LLC, intelligentbiz.net 9

Recommended DAX Tools
Power BI Desktop SSAS Tabular Excel 2010/SSAS 2012, 2014
/Excel 2013+

• Power BI Desktop • SSDT for Visual Studio • NotePad++

• Excel 2015+ • DAX Studio
• DAX Studio • SSMS • DAXFormatter.com
• Excel
• DAX Studio

© 2017 Intelligent Business LLC, intelligentbiz.net 10

 DAX
DAX Function Roadmap
Date & Time Time Intelligence Filter Information Logical Math Statistical

Some DATE()
TIME()
DATESMTD()
TOTALMTD()
FILTER()
VALUES()
ISBLANK()
ISERROR()
IF()
AND, &&, AND()
SUM()
AVERAGE()
SUMX()
AVERAGEX()
examples TODAY()
NOW()
PERVIOUSMONTH()
SAMEPERIODLASTYEAR()
ALL()
CALCULATE()
CONTAINS()
HASONEVALUE()
OR, ||, OR()
SWITCH()
DIVIDE() RANKX()
PERCENTILE()
DATEDIFF() DATESINPERIOD() TRUE()
EOMONTH()

Important DAX query engine flow: Context: Measures: Calculated

concepts • Get report filter context • Evaluation… Columns:
• Apply CALCULATE() filters • Filter… • Majority of all calculations • Good for banding
• Can have implicit or • Built-in row context
• Apply dimension table filter context • Row… explicit filter context • Using measures in calc.
• Filter fact table using relationships • Context • Can override natural filter columns will ignore row
• Perform math & calculations transition behavior context

© 2017 Intelligent Business LLC, intelligentbiz.net

Calculated Columns
• Typically used in the context of a single row
• May be used as intermediate calculations to support measures
Arrival Date Time =
This calculated column combines the flight date
IF( [ArrTime] < [DepTime], and arrival time values needed by measure
[FlightDate] + 1, calculations. It adds one day if the arrival time is
earlier than the departure time because the flight
[FlightDate] landed the next day (after midnight)
) + [ArrTime]

© 2017 Intelligent Business LLC, intelligentbiz.net 12

Measure Calculations
• Understanding context
• Row context
• Filter context
• Context switching
• The CALCULATE function revisited
• How DAX processes & calculates results
• Maintaining a development, testing and learning environment

© 2017 Intelligent Business LLC, intelligentbiz.net 13

CALCULATE function revisited

CALCULATE(<expression>, <filter1>, <filter2>)

Means: “Go apply filters”
Similar in concept to Where in TSQL

Avg Weath Delay for bad weather days =

CALCULATE(
AVERAGE( [Weather Delay] ),
'Airline Performance'[WeatherDelay] > 0
)

This measure calculates the average Weather Delay only for those flights where there was a delay (over 0 minutes)

© 2017 Intelligent Business LLC, intelligentbiz.net

How DAX Processes & Calculates Results
1. Get Row & Filter Context from
report visual (or Pivot Table here) Excel example:
2. Adjust the Filter Context based on …based on filters, slicers, rows& columns

CALCULATE() parameters
(if CALCULATE is part of the function)

3. Filter the related lookup tables

4. Pass those filters along to the Fact
table
5. Output a subset of rows
6. Do the math

© 2017 Intelligent Business LLC, intelligentbiz.net

Time Intelligence
DAX functions

• NOW, DATE, TIME

• DATEADD
• DATEDIFF
• DATESMTD
• DATESBETWEEN
• TOTALMTD …QTD, YTD
• NEXTMONTH …DAY, QUARTER, YEAR
• PARALLELPERIOD

© 2017 Intelligent Business LLC, intelligentbiz.net 16

Time Intelligence Example
Month-To-Date Average

Avg Weather Delay MTD = CALCULATE( [Avg Weather Delay],

DATESMTD( 'Flight Date'[Flight Date] ) )

© 2017 Intelligent Business LLC, intelligentbiz.net

17
Iterator functions

Mixed totals: Operates on one row at a time, accumulating the result of the prior iteration

• SUMX
• AVERAGEX
• MINX
• MAXX
• COUNTX
• COUNTAX
• PRODUCTX
• CONCATENATEX

© 2017 Intelligent Business LLC, intelligentbiz.net

Using Iterators
These two different calculation techniques achieve the same result using
different formula mechanics:
Avg Weather Delay =
AVERAGEX (
FILTER ( 'Airline Performance', 'Airline Performance'[WeatherDelay] > 0 ),
'Airline Performance'[WeatherDelay] )
For every row in the ‘Airline Performance’ table, the FILTER is applied and qualifying values are added to the Average calculation.
Although less efficient than the following example, complex operations can be performed within each iteration.

Avg Weather Delay =

CALCULATE(
[Avg Weather Delay],
'Airline Performance'[WeatherDelay] > 0 )

© 2017 Intelligent Business LLC, intelligentbiz.net 19

Dependent Measures
• Measures may be used in other measure calculations
• Measure names must be unique within the model
• Measures contained in any table can be used in any calculation

Flights = COUNTROWS( ‘Airline Performance’ )

All Flights = CALCULATE( [Flights],

ALL( 'Airline Performance‘ ) )

% of Total Flights= DIVIDE( [Flights], [All Flights] )

© 2017 Intelligent Business LLC, intelligentbiz.net

CALCULATE() with FILTER()
• Use FILTER() function for rich filtering
Flights Over 15 min = CALCULATE( [Flights],
FILTER( ‘Airline Performance’, [Avg Weather Delay] > 15 )
)

Comparison options: Function-based comparisons:

Table[Column] = [Measure]
Table[Column] = Table[Column]
[Measure1] > [Measure2]
<true/false expr1> && <true/false expr2> =AND( <true/false expr1>, <true/false expr2> )
<true/false expr1> || <true/false expr2> =OR ( <true/false expr1>, <true/false expr2> )
Any expression that evaluates to true/false

© 2017 Intelligent Business LLC, intelligentbiz.net

Financial Calculations
common scenario

• Chart of Account dimension

• Level Rollups
• Parent-child source table
• Unary Operators

© 2017 Intelligent Business LLC, intelligentbiz.net 22

Using Disconnected Tables
May be used to:
• Pass “parameters” to a measure
without filtering other model
elements
• Implement conditional measure
behavior
• Dynamically slice
specialized
measure calculations

© 2017 Intelligent Business LLC, intelligentbiz.net

Power BI and Excel
How is their relationship?

© 2017 Intelligent Business LLC, intelligentbiz.net 24

Use DAX Studio to Get Power BI Connection
• DAX Studio exposes the
random port number
Power BI Desktop uses
to connect to the local
SSAS server
(msmdsrv.exe)
• Use the connection
string localhost:XXXXX
to connect with Excel,
SQL Server Profiler,
SSMS or any other
OLAP client.

© 2017 Intelligent Business LLC, intelligentbiz.net 25

Recommended Practices
• Learn DAX concepts before function syntax
• Learn DAX essential functions… Learn these first:
SUM, AVERAGE, MIN, MAX, COUNT, COUNTROWS, CALCULATE, FILTER, IF
• Name measure so users can find them
• Don’t try to memorize complex DAX
• Build a library of useful examples, books & articles
• Work in iterations
• Understand measure categories:
aggregates, time & ratios, business-specific, KPI parts

© 2017 Intelligent Business LLC, intelligentbiz.net 26

Resources
• QuickStart: Learn DAX Basics in 30 Minutes
https://support.office.com/en-us/article/QuickStart-Learn-DAX-Basics-in-30-Minutes-51744643-
c2a5-436a-bdf6-c895762bec1a
• PowerPivotPro YouTube channel
https://www.youtube.com/powerpivotpro
• DAX Patterns, DAX Puzzle, DAX Formatter:
http://www.daxpatterns.com, SQLBI.com
• Books:

© 2017 Intelligent Business LLC, intelligentbiz.net

Contact Information
Paul Turley Presentation and
Intelligent Business LLC Materials:
http://tinyurl.com/MontrealPBI0517

e [email protected]
b SqlServerBi.blog
l linkedin.com/in/pturley
t @paul_turley

© 2017 Intelligent Business LLC, intelligentbiz.net