I have written a series of articles over the last few years including:
- What is Power BI
- What is Power Query
- What is Power Pivot
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.
Examples
| DAX | Excel | Comments |
|---|---|---|
| SUM(table[column]) | SUM(<range>) | |
| MAX(table[column]) | MAX(<range>) | |
| AVERAGE(table[column]) | AVERAGE(<range>) | |
| 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.
Implicit Measures
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).
Quick Measures
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?
Dear Mr. Matt Allington,
Can you help me? I am a beginner and I have a simple question.
I have a table “Table1” with columns – criteria 1, criteria 2, Year, Month, Forecast, Actual.
The Actual against the Forecast is considered by this formula:
% Act. Vs. FRC = sum(‘Tab 1′[Actual]) / SUM(‘Tab 1′[Forecast])
Each filter has different results. For example, December is 98% and for November it is 89%. How do I find the difference between them, which is 9%? How should this be done? (% diff. =?)
I would be grateful if you would help me.
Best Regards.
Nikey
Hi Nikey, best to ask at community.powerbi.com to get some help from the community.
Great article! I think DAX is an acronym and not an anagram.
Doh! Thanks for pointing that out – stupid mistake.
Here is what my experience was starting with Power Pivot 4 years ago. I stumbled across it internet searching for a new way to do reporting as I did not like my existing tool at all. I read a few articles, activated the plugin (Excel 2013) and made a go of it. I quickly got confused, purchased Rob Collie’s first book, read it in one night, came back to work the next day and knocked out some fairly sophisticated (at the time) reports. I impressed myself on what could be done with limited DAX knowledge.
But while Collie’s book was great at a beginning level, I quickly hit a wall when it came to fine tuning the pivots to get it to function precisely as needed. I started getting report requests I couldn’t get to work as my understanding of DAX at the time was limited so I couldn’t understand why i was getting wrong results. Without a solid understanding of DAX, filter context, context transition, etc. I fear people are going to not get the reports they want to work; or even worse rely on reports thinking they are accurate when they are not.
So to finally answer your question, I think a lot can be done with basic understanding of DAX – but I also think folks are going to hit a wall like I did. And to break through the wall will require a better understanding of the DAX language ( and Power Query / M ). But I also think DAX is quite learnable if people are willing to put some effort into it.
Edit/Copy/Paste – I had the exact same experience. I think most Excel pros need some help to get going with Power Pivot. I am just teaching a great class for a client (16 people) that reflect your same experience. It is so good to teach keen people that are ready to learn.
Great post Matt, I think you summarised DAX nicely. Although I’m nitpicky about the term “SQL Server Analysis Services Tabular Edition”. I think it should be Tabular Mode, as Edition is used in the Standard and Enterprise Edition naming convention when talking about SQL Server feature sets.
https://docs.microsoft.com/en-us/sql/analysis-services/instances/install-windows/media/ssas-setupasconfig.png
You are right James. I will use “mode” from now on