I have written a series of articles over the last few years including:
But I have never written an article about the DAX language, so I thought I would cover this off today.
What is DAX?
DAX is an acronym for Data Analysis eXpressions. DAX is the programming language of Power Pivot that is used inside the following commercial business intelligence products:
- Power Pivot for Excel
- Power BI
- SQL Server Analysis Services Tabular (SSAS Tabular)
Power Pivot, Power BI and SSAS Tabular are all business intelligence reporting tools that use the same underlying “engine”. This engine was originally called xVelocity (also Vertipaq) when Microsoft first developed the product. For the rest of this article I will use the term Power Pivot to refer to this engine across all products. Power Pivot is a user friendly business intelligence reporting database developed by the SSAS team at Microsoft.
Power Pivot was built from the ground up with care for Excel users (also SQL pros of course). In part as a result of this consideration, DAX was developed as a functional language. All this means is that when you use DAX to program in Power Pivot, you write functions to complete each task. This is a very important point because Excel is also a functional language. This is a big change from historical business intelligence programing languages (eg MDX) which were very difficult to learn.
Many of the DAX functions are either identical or similar to functions in Excel, and this ensures relative consistency for the targeted user base.
|OR(<Input1>,<input2>,<input n>)||OR(<condition 1>,<condition 2>)||Note that Power Pivot only has 2 parameters|
As you can see above, the functions are often identical in syntax, but sometimes they are not, as is the case with the OR() function.
Unfortunately the similarities between the DAX language and Excel functions creates a certain level of over confidence in most Excel users to the point that most people underestimate how hard it actually is to understand Power Pivot. The problem is that people transitioning from traditional Excel to Power Pivot need to learn to work with data in tables, columns and rows instead of independent cells. I have taught thousands of students the DAX language and this is by far the biggest hurdle for Excel users to get started. Once an Excel user makes the transition from working with cells of data and learns to work with tables of data, they are off and running with a new kit bag of skills. If you are not there and need help, I have a number of learning options including my book, my online training or my live training in Australia.8
Do I need to learn the DAX language?
You certainly do not need to know how to write DAX to get started with Power BI. Power BI is the newest business intelligence tool that leverages the DAX language (via Power Pivot) and it is definitely possible to get started and build some reports without learning any DAX at all. If you are a “consumer of reports” that other people produce for you then you certainly don’t need to learn any DAX. However if you are someone that wants to do your own adhoc (or structured) analysis of data using Power BI, Power Pivot for Excel, then you will definitely want to learn to write some DAX in order to get value from what these new tools have to offer.
Both Power Pivot for Excel and Power BI offer what are called “Implicit Measures”. All you have to do is drag a column of numbers (eg Sales Value) and drop it in a Values section of a Pivot Table or a Power BI Visual, and the tool will create a nice aggregation for you on the fly.
And as long as that is all you ever want to do, then you will be fine. However if you want to create a more powerful reporting tool that extracts deep insights from your raw data, then you will definitely want to start your journey to learn DAX (more on this shortly).
Power BI has a relatively new feature called Quick Measures. This feature allows you to build true measures using a wizard. While this feature may get you started and does write true measures (not implicit measures like written above), the downside is you are still not truly learning about how Power Pivot works under the hood. I do like this feature however I don’t think it is a substitute for some more structured learning.
Adding Business Logic into your Data Models using DAX
The DAX language is used to add your business logic into the database (data model). An example will help clarify. It is typical that data from a sales system will contain
- Cost Price for the transaction
- Sell Price for the transaction
But the database may not include:
- Margin for the transaction
- Margin % for the transaction
There is some “business logic” that can be used to take the raw data (1 and 2) and generate the Margin and Margin % figures (3 and 4). The business logic is simple
- Margin = [Sell Price] – [Cost Price]
- Margin % = [Margin] / [Sell Price]
The data modeller (the person that works with Power Pivot to build a report) will use the DAX language to convert the “Business Logic” into “Measures” so that the users of the report can directly access all the reporting features needed to add value.
In the image below, the measures shown as 1 “could” be created as an implicit measure without learning DAX, but the more useful measures shown as 2 below require the DAX language.
DAX as a Query Language
As well a being a data modelling language, DAX is also a query language. There are some special DAX functions that return “tables” of data rather than scalar values. I have extensive coverage of DAX as a query language over a series of 3 blog articles starting here.
What is Your Experience?
What has been your experience? Have you been able to do what you need without DAX, or do you see value in learning this skill?