This blog is essentially designed for users new to the Power BI Desktop and is meant to give you a quick and easy walkthrough on a formula-language called Data Analysis Expressions (DAX). If you’re familiar with functions in MS Excel or SQL, many of the formulae in Power BI DAXwill appear similar to you.
Having said that, here are the concepts, which form an integral part of all Power BI curricula, after learning which you should have a good understanding of the most fundamental concepts in DAX.
So, it’s quite easy to create reports using the Power BI Desktop, that’ll show valuable insights straight off the bat.
But, what if you need to analyze growth percentage across all the product categories, for all the different date ranges? Or, you need to calculate the annual growth of your company compared to market giants?
DAX comprises of functions, operators, and constants that can be put into the form of formulae to calculate values with the help of data already present in your model.
The Power BI DAX includes a library of over 200 functions, operators, and constructs. Its library provides immense flexibility in creating measures to calculate results for just about any data analysis need.
How does it work?
First of all, let me explain to you how this works. We shall, for the most part, be framing our understanding of Power BI DAX around three fundamental concepts: Syntax, Context, and Functions.
Of course, there are other important concepts in here, but understanding these three will provide the best foundation on which you are going to build your skills.
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 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-Contextis 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-Contextis 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 particular value, Store is used as a filter. This is our Filter-Context.
This formula ensuresthatthe Total Sales Measure are calculated only for rows in the Channel[ChannelName] Column with the value “Store”, as a filter.
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 & Measures
In this blog, we’re going to be focusing on the Power BI DAX formulae used in calculations, in Measures and 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 behavior 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.
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 have to 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.
Types of Functions in Power BI DAX
1. Aggregate Functions
This DAX function returns the minimum numeric value in a column, or between two scalar expressions.
This DAX function returns the minimum value in a column, including any logical values and numbers represented as text.
This DAX function returns the minimum numeric value that results from evaluating an expression for each row of a table.
This DAX function rounds a number to the specified number of decimals and returns the result as text.
FIXED(, , )
This DAX function replaces part of a text string, based on the number of characters you specify, with a different text string.
REPLACE(, , , )
=REPLACE('New Products'[Product Code],1,2,"OB")
This DAX function returns the number of the characters at which a specific text string is first found.
SEARCH(, [, [, ]])
The formula returns 4 because "n" is the fourth character in the word "printer."
This DAX function returns a text string in all uppercase letters.
=UPPER(['New Products'[Product Code])
Creating your First Measure using Power BI DAX
Prerequisite: You’ll need to open this given Power BI Desktop file.
Since I’m assuming this is going to be your first, I’ll be writing this bit in great detail for you to follow along.
In the field list of the Report View, right-click on the Sales table, followed by the New Measure.
Replace Measure by typing a new measure name Previous Quarter Sales, in the Formula Bar.
In this formula, you want to use the CALCULATE function. So, after the equals sign, type the first few letters CAL, and then double-click the function you want to use.
The CALCULATE function has at least two arguments. The first is the expression to be evaluated, and the second is a Filter.
After the opening parenthesis( for the CALCULATE function, type SUM followed by another opening parenthesis ( to pass an argument to the SUM function.
Begin typing Sal, and then select Sales[SalesAmount], followed by a closing parenthesis ). This is the first expression argument for our CALCULATE function.
Type a comma (,) followed by a space to specify the first filter, and then type PREVIOUSQUARTER. This will be our filter.
You’ll use the PREVIOUSQUARTER time intelligence function to filter SUM results by the previous quarter.
After the opening parenthesis ( for the PREVIOUSQUARTER function, type Calendar[DateKey].
The PREVIOUSQUARTER function has one argument, a column containing a contiguous range of dates. In our case, that’s the DateKey column in the Calendar table.
Make sure both the arguments being passed to the PREVIOUSQUARTER and the CALCULATE function are closed by typing two closed parentheses )).
Your formula should now look something like the following; Previous Quarter Sales = CALCULATE(SUM(Sales[SalesAmount]), PREVIOUSQUARTER(Calendar[DateKey]))
Click the checkmark in the formula bar or press Enter to validate the formula.
Once you’ve added that to your model, voila! You just created a measure using DAX, and not an easy one at that.
What this formula does is calculate the total sales for the previous quarter, depending on the filters applied in a report.
So, let’s if we had to put SalesAmount and our new Previous Quarter Sales measure in a chart, and then added Year and QuarterOfYear as Slicers, we’d get something in the likes of the following;
Now that you have a basic understanding of the concepts in Power BI DAX, you can begin creating DAX formulas for measures on your own. Indeed, it can be a little tricky to learn, but DAX has been around for several years and there are many resources available on the web. After reading through this blog and a little experimentation, you can learn to find business solutions through Power BI DAX.