What is DAX

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.

Examples

Function Comparison
Excel Power Pivot Comments
SUM(<range>) SUM(table[column])
MAX(<range>) MAX(table[column])
AVERAGE(<range>) AVERAGE(table[column])
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.

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.

Implicit Measures

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.

implicit measures

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 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

    1. Cost Price for the transaction
    2. Sell Price for the transaction

But the database may not include:

  1. Margin for the transaction
  2. 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.

image

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?

Share?

Comments

  1. 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.

Leave a Reply to Matthew Brice Cancel reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x