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 BI that is used inside the following commercial business intelligence products:
- Power BI
- Power Pivot for Excel
- SQL Server Analysis Services Tabular (SSAS Tabular)
Power BI, Power Pivot 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 BI Data Model to refer to this engine across all products. Power BI Data Model is a user friendly business intelligence reporting database developed by the SSAS team at Microsoft.
Power BI Data Model 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 BI Data Model, 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 programming languages (e.g. 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(<condition 1>,<condition 2>)||OR(<input1>,<input2>,<input n>)||Note that DAX has only 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 functions 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 BI Data Model. The problem is that people transitioning from traditional Excel to Power BI 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.
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 BI Data Model) 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, then you will definitely want to learn to write some DAX in order to get value from what this new tool has to offer.
Power BI offers what are called “Implicit Measures”. All you have to do is drag a column of numbers (e.g. ExtendedAmount) and drop it in Values section of 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 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 BI Data Model 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 Model using DAX
The DAX language is used to add your business logic into the Power BI database (data model). An example will help clarify. It is typical that data from a sales system will contain the raw data (1 and 2) but database may not include (3 and 4).
- Cost Price for the transaction
- Sell Price for the transaction
- 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 BI data model 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 implicit measures without learning DAX, but the more useful measures shown as 2 below require the DAX language.
DAX as a Query Language
As well as 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?