DAX in Power BI

What is DAX?

DAX (Data Analysis Expressions) is a formula expression language and can be

used in different BI and visualization tools. DAX is also known as function
language, where the full code is kept inside a function. DAX programming
formula contains two data types: Numeric and Other. Numeric includes
- integers, currency and decimals, while Other includes string and binary
object.

Power BI DAX has three fundamental concepts: Syntax, Context, and Functions.

Syntax

The Syntax comprises of various components that make up a formula and how
it’s written. Look at this simple DAX formula.
When trying to understand a DAX formula, it is often helpful to break down
each of the elements into a language you think and speak every day. So, this
formula includes the following syntax elements:

I. Total Sales is the measure name.

II. The equals sign operator (=) indicates the beginning of the formula.

III. SUM adds up all of the numbers in the column, Sales[SalesAmount]. 

IV. There are these parentheses () that surround an expression containing one
or more arguments. All functions require at least one argument. 

V. Sales is the table referenced.

VI. An argument passes a value to a function. The referenced

column [SalesAmount] is an argument with which, the SUM function knows
the column on which it has to aggregate a SUM.

Simply put, you can read it as, “For the measure named Total Sales, calculate
(=) the SUM of values in the [SalesAmount ] column in the Sales table.”

The Power BI DAX editor includes a suggestions feature, which helps you create
syntactically correct formulas by suggesting you the right elements.

Context

Context is one of the most important of the 3 DAX concepts. When one speaks
of context, this may refer to one of the two types; Row context and Filter
context.

Used predominantly whilst speaking of Measures, the Row-Context is most

easily thought of as the current row. It applies whenever a formula has a
function that applies filters to identify a single row in a table.

Filter-Context is a little more difficult to understand than the Row-Context. You

can most easily think of the Filter-Context as one or more filters applied in a
calculation. The Filter-Context doesn’t exist in the Row-context’s stead. Rather,
it applies in addition to the former. Look at the following DAX formula.

This formula includes the following syntax elements:

I. The measure name Store Sales.

II. The equals sign operator (=) indicates the beginning of the formula.

III. The CALCULATE function evaluates an expression, as an argument.

IV. Parenthesis () surround an expression containing one or more arguments.

V. A measure [Total Sales] in the same table as an expression.

VI. A comma (,) separates the first expression argument from the filter
argument.

VII. The fully qualified referenced column, Channel[ChannelName] is our Row-

Context. Each row in this column specifies a channel, Store, Online, etc.

VIII. The value, Store is used as a filter. This is our Filter-Context.

OZ_Sales = CALCULATE(SUMX(Sales, Sales[Amount]),

Sales[Customer.Country]="OZ")

This formula ensures that the Total Sales Measure are calculated only for
rows in the Channel[ChannelName] Column with the value “Store”, as a
filter.

Functions

Functions are predefined, structured and ordered formulae. They perform

calculations using arguments passed on to them. These arguments can be
numbers, text, logical values or other functions.

Calculated Columns

When you create a data model on the Power BI Desktop, you can extend a
table by creating new columns. The content of the columns is defined by a DAX
expression, evaluated row by row or in the context of the current row across
that table. 

 In data models for DAX, however, all calculated columns occupy space in
memory and are computed during table processing.

This behaviour is helpful in resulting in better user experience, but it uses

precious RAM and hence, is a bad habit in production because each
intermediate calculation is stored in RAM and wastes precious space.

Measures

There is another way of defining calculations in a DAX model, useful if you

need to operate on aggregate values instead of on a row-by-row basis. These
calculations are measures. One of the requirements of DAX is that a measure
needs to be defined in a table. However, the measure does not really belong to
the table. So, you can move a measure from one table to another one without
losing its functionality.

Calculated Columns vs Measures

Measures and calculated columns both use DAX expressions. The difference is
the context of evaluation. A measure is evaluated in the context of the cell
evaluated in a report or in a DAX query, whereas a calculated column is
computed at the row level within the table it belongs to.

Even if they look similar, there is a big difference between calculated columns
and measures. The value of a calculated column is computed during a data
refresh and uses the current row as a context; it does not depend on user
interaction in the report.

Hence, you must define a calculated column whenever you want to do the
following;

 Place the calculated results in a slicer or see results in rows or columns in

a pivot table (as opposed to the values area), or in the axes of a chart, or
use the result as a filter condition in a DAX query.
 Define an expression that is strictly bound to the current row. For
example, Price * Quantity cannot work on an average or on a sum of the
two columns.
 Categorize text or numbers. For example, a range of values for a
measure.
A measure operates on aggregations of data defined by the current context,
which depends on the filter applied in the report – such as slicer, rows, and
columns selection in a pivot table, or axes and filters applied to a chart.

So, you must define a measure whenever you want to display resulting
calculation values that reflect user selections, such as;

 When you calculate the profit percentage on a certain selection of data.

 When you calculate ratios of a product compared to all products but
keeping the filter both by year and region.

Example: Profit as a calculated column

Expression:1Profit=FactInternetSales[SalesAmount]-
FactInternetSales[TotalProductCost]

Example: Sum of Sales (Measure)

Measures are usually aggregations. A very simple aggregation we can use as an
example is a sum of sales.

Aggregation can be done with some functions in DAX, such as Sum, SumX,
Average, Calculate, and heaps of other aggregation functions. Now, let’s
answer the most important question:

How to see the Value of the Measure?

Measures are calculated on the fly. This is, in fact, one of the most conceptual
differences between a measure and calculated column. Okay, measure values
are calculated on the fly, so how you can see the value?!

The answer is by putting that into a report! If I drag the measure above in a
report as a card visual, then I would get a result

Example 1: Calculating the age of customers

Age of customers does not change based on filters! It is only dependent on one
thing; birthdate of the customer. Andin the customer table, usually, you have
the birthdate as a field. So this calculation can be simply a calculated column,
which evaluates row by row for every customer

Age = DATEDIFF(DimCustomer[BirthDate],TODAY(),YEAR)

Example 2: Calculating Sales Year to Date

Year to date calculation depends on the filter criteria in the report, and it is an
aggregation. It becomes very complicated to calculation year to date for all
variations of fields (per day, per month, per customer, per product, etc). So this
needs to be a Measure

Sales Year to Date =

TOTALYTD(SUM(FactInternetSales[SalesAmount]),DimDate[FullDateAlternateK
ey].[Date])

Summary: Calculated Column vs.Measure in

a nutshell
Scenarios of Using Calculated Tables
in Power BI
Calculated tables first introduced in September 2015
update of Power BI Desktop. The name speaks for itself;
these are tables created by calculation. As these are in-
memory tables, their calculation is based on DAX (Data
Analysis eXpression language).

There are many benefits of using Calculated tables, such

as using them for role-playing dimensions (for example
having more than one date dimension in a model), There
are some DAX functions and expressions that returns a
table as a result and using them as a table in your model
sometimes is helpful.

For example, you might want to create a table for top 10

customers and then use that as the main source table in
many reports.

Role Playing Dimension

The very first functionality that appears in mind when we
talk about Calculated Tables is the ability to create role play
dimensions. Role Play dimensions are dimensions with the
same structure and data rows that play different roles in our
data model.
For example, Date Dimension is a generic dimension.
However, in a sales transaction table, you might have more
than one date columns to relate with the date dimension. In
the example below we have three date fields in
FactInternetSales table: Order Date, Ship Date, and Due
Date.

Now We can create role-playing dimensions with creating a

Calculated table:
And this will create a table in memory for me and allows me
to write the definition of the table
We want an exact copy of the DimDate table here. So We
can use ALL function in DAXas below

ShipDate=ALL(DimDate)
Now we can set up the relationship

DAX Table Functions

Some DAX functions return a table. For example, ALL
function which I used in the role-playing sample above. ALL
was a simple example of a DAX function that returns the
whole copy of the source table. Let’s have a look at some
other examples and see how it works in other scenarios.

Top 100 Customers as a Calculated Table There are

many examples that a business considers top 10 or top 20
customers and filter down the whole dashboard and set of
reports only for them.
Usually, the main reason is that top 10, 20 customers will
bring most of the revenue to the business. Fortunately,
there is a TOPN function in DAX which helps us to build
such calculation.
TOPN function returns a table. With TOPN we can choose
how many rows we want in the result set, and the grouping
function to be applied.

In this example, we want to show you how to use a

calculated table to generate a list of top 100 customers. As
a business requirement, We want to visualize the total
revenue from the top100 customers and compare it with
total revenue of the whole business. There might be
different ways to calculate and visualize it, but we want to
do it with a calculated table as a sample scenario.

Summarize
Summarize is a DAX function that generates a grouped by
list from a table. Summarize works like Group By in T-SQL.
So if I want to create a table with CustomerKeys and their
total sales amount, We can write this

expression:1CustomerSales=SUMMARIZE(FactInternetS
ales,FactInternetSales[CustomerKey],"TotalSales",SUM(F
actInternetSales[TotalSales])

TOPN
Now that we have the list of customers with their total sales,
it is easy to get top 100 customers. We can use TOPN
function like this to create another calculated table (We
could do this example with only one calculated table instead
of two, but We only did it with two tables to help you
understand the logic better)
Top100Customers=TOPN(100,'CustomerSales','Custome
rSales'[TotalSales],DESC)
Now we can simply build a report in Power BI to show the
difference between Total Sales and Top Customer Sale

SUM vs.SUMX;
What is the Difference of the two
DAX Functions in Power BI?

SUM: Aggregation Function

SUM is a simple aggregation function. It summarizes a
value based on a filter context. For example, if I have a
measure like

SumofSales=SUM(FactInternetSales[SalesAmount])

SUMX: Some of an Expression

Let’s now calculate the sum of margin, which is: the sum of
sales minus cost. This calculation is considering that we do
NOT have a column as a margin in our model, and we do
not want to create that column. Let’s see how it is possible
through a measure to calculate the sum of Margin. Margin
calculation is:
SalesAmount –TotalProductCost
But you cannot write a measure like below

When you start writing that measure, you don’t even get the
DAX intelligence for the second part of your expression:
SumX(<tablename>,<expression>)
For SUMX to work, you need to specify a table name.
When you use SUM, you do not need a table name,
because one column only belongs to one table. But when
you use SUMX, you may write an expression which uses
columns from other tables. In the example for Margin, both
columns are coming from the same table;
FactInternetSales. So, our expression would be:

SumofMargin=SUMX(FactInternetSales,FactInternetSales
[SalesAmount]-FactInternetSales[TotalProductCost])

Brief of functions
IF

“IF” is a conditional filtering expression function for DAX.

You can write a conditional expression including Then and
Else part of it. It simply works with this
syntax;
1IF(<conditionalexpression>,<whathappensiftrue>,<whath
appensiffalse>)

Filter
“Filter” is a function that filters data set based on a custom
filter. For example,you can filter only products with “Red”
color. Here is an example Filter expression

FILTER(<table>,<filtercondition>)
Sample Data Set
For this example, you need to bring FactInternetSales, and
DimProduct into your Power BI Model.
The relationship between these tables automatically should
be detected by Power BI.
It should be based on ProductKey between two tables.
Here is how the relationship looks like;
Conditional SUM
There are multiple ways of calculating conditional sum in
DAX. You can use SUMX or CALCULATE. Both functions
calculate an expression based on a filter (which would be
our conditional expression to find “Red” products).
We will use SUMX in this example, but the same concept
applies to Calculate function as well. Here is how you can
use SUMX for calculating the sum of “Red” products.

Method 1 –SumX with FILTER

In the first method, I can use SUMX expression and filter
the data set to be only “Red” products. Create a new
Measure in FactInternetSales with this expression;

SumofRedProducts – With Filter = SUMX(

FILTER(FactInternetSales,
RELATED(DimProduct[Color])="Red")
,FactInternetSales[SalesAmount]

We have used the RELATED function because Color is a

column in DimProduct and Related Function goes through
the relationship from Many (FactInternetSales) to One
(DimProduct) and allow us to do the filtering based on a
column in a related table.

Method 2 –SumX with IF

We can achieve the same result with SUMX and IF
condition together. In this case, the condition comes as IF
statement in the expression part of SUMX. Here is the new
measure’s code;

SumofRedProducts-WithIF=SUMX(
FactInternetSales,
IF(RELATED(DimProduct[Color])="Red",
FactInternetSales[SalesAmount],0))

In this expression, instead of filtering data with FILTER

function, We have used a conditional expression to identify
if the color of the product is “Red” or not, if it is “Red”, then
We use SalesAmount for sum calculation, otherwise We
use zero (means don’t summarize for other product colors).
Method 3 – Calculate with Simple Conditional
Expression
There are many other methods of calculating the
conditional sum, but just adding this one because it looks
different; If I use Calculate Function with the simple
expression for checking the color of the product as a new
measure; SumofRedProducts-
CalculateSimpleExpression=CALCULATE(
SUM(FactInternetSales[SalesAmount]),
DimProduct[Color]="Red")
Writing DimProduct[Color]=”Red” in this way is similar to
writing a condition for every result set. The result will be
sum of Red Products.
If you use “Sum of Red Products –With IF” only in a table
with “Color” from DimProduct, you will see the different
result;
In both cases, the total is similar. However the table with a
FILTER measure will automatically filter the data set, and
only shows the result set for RED products, where the
second table with IF measure, will show all products with
Real-world scenarios of DAX Expressions

DAX Time Intelligence for Power BI

Time Intelligence functions in DAX are a set of functions
that give you insight from the date and time dimensions.
Most of the analysis by date and time are in that category,
as an example; year to date, quarter to date, month to date,
same period last year calculations etc.
These calculations they all have one dimension in common;
date/time dimension.

Before Using DAX Functions

Before using any of the DAX functions that give you time
intelligence output, you must know that there is a
requirement for all these functions to work.
If you want to work with these functions, you HAVE TO
have a date in your data model. This date dimension should
have some requirements;
The date dimension that is acceptable by time intelligence
functions of DAX should;
 have one record per day
 start from the minimum date in the date field or before
that, and ends at the maximum date in the date field or
later than that.
 have no date missing (if there are no sales in the 1st of
January, still that date should be in this table. This is
 one of the reasons why you do need to have a
separate date table)

Year to Date; TotalYTD

Let’s start the example, with a simple function to calculate

year to date. The Year to date calculation is an aggregation
of value from the beginning of the year to the specified
date.
For example, Year to date value of sales can be the
summary of all sales from the 1st of January of that year to
the date specified. There is a function in DAX specifically
for the year to date calculation, named TotalYTD. here is
the signature of TotalYTD function;
TotalYTD( <expression>, <dates>, [<filter>], [<year end
date>])

Year to Date Another Approach: DatesYTD

TotalYTD is one of the methods of calculating the year to
date value. There is another way, that can be helpful in
more complex DAX expressions when you want to combine
multiple filter criteria together.
That way is using a function called DatesYTD combined
with Calculate function. DatesYTD is a function that accepts
only two parameters, which one of them is optional;
DatesYTD(<dates>, <year end date>)
DatesYTD returns a table as the output; a table with all
dates in the period of a year to date. That is why for
calculating the aggregation on that date range, it needs to
be used with a function such as Calculate.
Here is how the calculation works with DatesYTD function;

You may ask which one is a preferred option?

TotalYTD or DatesYTD.
The answer depends on the type of filter you are using, If
you are using multiple filter criteria, then I would suggest,
DatesYTD, because when it is used inside a Calculate, then
you can apply whatever filter you want.
You may be able to do that still with TotalYTD, but you
probably make the expression a bit complicated.
Fiscal or Financial Year to Date
Calculating the calendar year to date was easy, how about
the fiscal or financial calculation?
do we have a function for those?
No. However, there is a parameter that you can add in the
expression and that makes the calculation fiscal.
The <year end date> parameter is an optional parameter
which we have not used in the previous example.
When you do not assign a value for this parameter, the
default value would be considered which is 31st of Dec of
each year.
If you want to specify a value for this parameter, this is an
example of how you can do that:

As you can see the structure I provided the year-end date,

was month/day.
There are a few other options you can use as well, such as
06-30, 6/30, June 30, or 30 June. Anything that resolves a
month/day scenario.
It is, however, recommended to use month/day format.
“06/30” value as the parameter here means that the end of
the fiscal year is 30th of June of each year, and start as the
result would be 1st of July of the year. Here is the output,
as you can see the calculation restarts in July of each year
instead of the calendar year to date which is starting from
January.

The approach is very similar if you want to use DatesYTD

approach

Quarter to Date Calculation: TotalQTD

When you know how the calculation of the year to date
works, you can guess how it would work for the quarter to
date as well.
The only difference is the function for the quarter to date, is
called TotalQTD. It can be used exactly similar to the way
that we used TotalYTD in the previous example;

As you can see, this calculation, accumulate sales values

up to the end of each quarter.
Quarter to Date Calculation: DatesQTD
Similar to the DatesYTD, there is also a function for
DatesQTD, which can be used exactly the same way. here
is the code for it:

Month to Date Calculation: TotalMTD

To calculate the month to date, you can use TotalMTD
function very similar to the other functions you have seen in
previous examples. here is the code:

The same approach can be applied for the DatesMTD as

below;
There are many time intelligence functions in DAX, and
each of those is helping in aspects of analysing data on
dates. There are two functions which work very similar to
each other but have a bit different usage; DatesInPeriod,
and DatesBetween. We will show you what the difference
between these two functions, and scenarios that you can
use each is. DatesBetween and DatesInPeriod both give
you a period of dates, but let’s see their main difference.

DatesInPeriod
The DatesInPeriod function in DAX will give you all dates
within a period. The period can be one of these: Day,
Month, Quarter, Year. Here is the syntax of using this
function;

DATESINPERIOD(<dates>,<start_date>,<number_of_int
ervals>,<interval>)
Here is a description of input parameters;
•<dates>: The date field (like many other time intelligence
functions, this function also requires a date field)
•<start_date>: The start date that period starts from/to it
(depends if the interval is a positive or negative number)
•<number_of_intervals>: a positive or negative number that
starts from the start date based on the interval
•<interval>: Year, Quarter, Month, or Day intervals The
output of this function is a table of dates within the period
specified.
Let’s see how this function can be used. For example, If
you want to get all dates in the last year’s period from the
date of the filter context, it can be a calculation like this;

DATESINPERIOD(FactInternetSales[OrderDate].
[Date],LASTDATE(FactInternetSales[OrderDate].[Date]),-
1,YEAR)
Note that FactInternetSales[OrderDate] is just a normal
date field in the FactInternetSales table and the reason that
I used “.[Date]” at the end of it, is because I am using the
built-in date dimension of Power BI.
If you use your date dimension and have set it as a date
table, then you should exclude the “.[Date]” part of this
expression.
To get the current filter context’s date as the start date, I
used theLASTDATE()DAX function, and we are going a
Year back in the interval. Sothe number of intervals is–1.
The expression above returns a table, and cannot be used
as a measure. To show you how this can work, I put it
inside another function as a measure

Example: Sales for the Last Rolling Year from the

current Date
An example of using DatesInPeriod is to calculate the sales
of the last year from the current date. In the expressions
above, you’ve seen how we can get all dates in the last
year from the current date in the filter context. We need to
put it inside a Calculate statement to get Sum of Sales for
that period.
SalesfortheLastRollingYear=CALCULATE(SUM(FactInter
netSales[SalesAmount]),DATESINPERIOD(FactInternetS
ales[OrderDate].
[Date],LASTDATE(FactInternetSales[OrderDate].[Date]),-
1,YEAR))
What is the period of the calculation?

The important question in the above calculation is that what

is the period of the calculation? Is this from the first of the
year? Or is it starting from a different date? What is
included and what is excluded?
The answer is that; DatesInPeroid starts from the
<start_date> (which in this case is the month in every row
of the table visualized in the screenshot above), and it will
go one year back (because the interval is the year, and the
number of intervals is -1).
For example; If the current month April 2007, then it will go
one year back from that date. But does it mean it will start
from April 2006, or May 2006? Well, DatesInBetween is a
smart function and will exclude the start date to avoid
double counting.It will start in May 2006.
Let’s see what the period start and period end is.To get the
period start and period end, you can create two measures
below using FIRSTDATE() and LASTDATE() functions;

FirstDateinthePeriodforDatesInPeriod=FIRSTDATE(DATE
SINPERIOD(FactInternetSales[OrderDate].
[Date],LASTDATE(FactInternetSales[OrderDate].[Date]),-
1,YEAR))
and for the last date;

LastDateinthePeriodforDatesInPeriod=LASTDATE(DATE
SINPERIOD(FactInternetSales[OrderDate].
[Date],LASTDATE(FactInternetSales[OrderDate].[Date]),-
1,YEAR))

Now you can see the period clearly in Power BI

As you can see in the yellow highlighted section; for April
2007, the Rolling Last Year Sales is $5,994,882.35, which
is for the period between the 1st of May 2006 to 30th of
April 2007.
As you can see it starts not from the 30th of April 2006 to
avoid double counting. DatesInPeriod makes your like
much easier to calculate dates in a period.
That is why it is called DatesInPeriod! So the value of
Rolling Last Year Sales is the accumulation of all sales from
May 2006 to April 2007.

DatesInPeriod is perfect DAX function for

calculating standard periods which follow Day,
Month, Quarter, and Year intervals. It will exclude
unnecessary dates for you.

DatesBetween
DatesBetween function in DAX is a more generic version of
DatesInPeriod. You have more flexibility with this function.
With this function, youdo not need to worry about the
interval or number of intervals. This function will give you all
the dates between a start date and an end date. Here is the
syntax of this function;

DATESBETWEEN(<dates>,<start_date>,<end_date>)
Parameters are:•<dates>:The date field (like many other
time intelligence functions, this function also requires a date
field)
•<start_date>: The start date that period starts from it
(unlike DatesInPeriod, this cannot go backward from the
start date. It always go forward from there)
•<end_date>: The end date that period ends there. The
output of this function is a table of dates from the start_date
to the end_date including both start and end date. An
important understanding of this function is that the function
itself doesn’t go back or forth from the start date to give you
the period. You have to calculate the start or the end date
first, and then get the period based on that.
For example; Let’s say we want to calculate dates in the
last rolling year from the current date in the filter context
(similar to the example we have done with DatesInPeriod).

You need first to find out what your start date is.

DATEADD(LASTDATE(FactInternetSales[OrderDate].
[Date]),-1,YEAR)
The expression above is using DATEADD() function to
calculate the start date which is going to be a year before
(because the interval is -1) from the start date, which is
calculated with LASTDATE().
After calculating the start date, you can use it inside a
DatesBetween function like this

DATESBETWEEN(FactInternetSales[OrderDate].
[Date],DATEADD(LASTDATE(FactInternetSales[OrderDat
e].[Date]),-
1,YEAR),LASTDATE(FactInternetSales[OrderDate].
[Date]))
The first parameter is just the date field. The second
parameter is the start date that we have calculated, and the
last parameter is the end date. DatesBetween is a good
function to use when the start and end of the period are
determined. Here is an example of calculating the sale of a
specific period.

SalesofSpecificPeriodwithDatesBetween=CALCULATE(S
UM(FactInternetSales[SalesAmount]),DATESBETWEEN(
FactInternetSales[OrderDate].
[Date],DATE(2007,8,1),DATE(2007,11,16)))

DatesInPeriod vs DatesBetween
Now let’s see if we use the DatesBetween for calculating
the period and get the start and end of that period what we
get as a result;

FirstDatesBetween=FIRSTDATE(DATESBETWEEN(FactI
nternetSales[OrderDate].
[Date],DATEADD(LASTDATE(FactInternetSales[OrderDat
e].[Date]),-
1,YEAR),LASTDATE(FactInternetSales[OrderDate].
[Date])))

and the calculation for the end of the period;

LastDatesBetween=LASTDATE(DATESBETWEEN(FactI
nternetSales[OrderDate].
[Date],DATEADD(LASTDATE(FactInternetSales[OrderDat
e].[Date]),-
1,YEAR),LASTDATE(FactInternetSales[OrderDate].
[Date])))

The output of DatesBetween INCLUDES both start and end

date, it will start from 30th of April 2006, while the
DatesInPeriod starts from 1st of My 2006. so the first
difference between these two functions is that one of the is
inclusive of both dates (DatesBetween).

If you have the start and end date, and you want to
get all dates in that period, Dates Between is a
good function to use. However, Sometimes, you
do not have both ends of the period, you have
one, and the interval, in that case, DatesInPeriod
is your best friend