With the release of PowerPivot in 2010, Microsoft also released programming language called DAX. Beside PowerPivot, DAX is also used in Microsoft SQL Server Analysis Services (SSAS). It’s a formula language which operates against the data model.
What makes this language so special? Its ability to calculate complex formulas in a dynamic environment with high execution speed. With DAX under the hub of PowerPivot, your complex models with many million rows of data coming from various sources will run in a matter of split second.
With more advanced solutions DAX code could become slower, but you can always optimize it to get the best performance, and in that circumstances, other BI tools would also show their flaws.
Difference between DAX and other Programming languages
Unlike in other programming languages, you do not have objects, looping, programming blocks etc. Instead, you use a combination of functions, filters and expressions, all pretty similar to Excel formulas. For each calculation in a data model, you need to create a formula. What is good about it is that you can use newly created formulas (called measures) in different places of your data model. Same calculation steps will apply, just under different evaluation context. You can build measures based on other measures to bring sophisticated lively models. Every model you used to create using Excel formulas can be done through measures, and much more!
If you are coming from an Excel background, you will find similarities between Excel and DAX formulas. Some formulas are the same and the logic of creating formulas is alike. Yet, you cannot just upgrade Excel formula knowledge to meet DAX requirements. The main reason is that DAX doesn’t understand cell coordinates (e.g. cell A3 or range A3:H9). DAX formulas always refer to whole columns or tables.
Then how do you create formulas that are meant to calculate just a part of column values? In DAX you use filters combined with already existing filter context to determine on which subset your formula should calculate values. This is a pretty basic explanation of how DAX calculations work.
A more detailed explanation would involve Filter&Row Context, and Evaluation context as a result of previous 2, then context transitions and few other main concepts which you need to understand to create complex DAX measures. But the point of this article is not to go too deep into DAX concepts, which I will describe in my upcoming article.
Why DAX?
Since it is a formula language, you do not need any of programming concepts mentioned earlier. This makes it is more suited for non-IT personnel. Emphasis is put on advanced Excel users, who wish to evolve their skill set to meet nowadays requirements for Big Data analysis.
If you are a regular user of PivotTables, you will be amazed by the possibilities DAX provides to your “old” PivotTable. Many situations where you needed redundant columns or advanced formula now is the thing of the past. Even with simple calculations, like the one I will show you next, DAX measures prove to be superior to regular formulas/PivotTables.
Imagine you want to build KPI tracking YTD (Year to Date) sales performance to see if you are performing better compared to last year figures. To build this simple KPI table, you need YTD TY vs YTD LY % change formula and sales per month (to track trend). Of course, you want this table to be dynamic, meaning when figures of the next month arrive, your model should auto adjust to include new data in the KPI table.
In the picture below, on the left side is the table that we need so we could report YTD change. On the right side of the picture you can see one of the approaches with regular PivotTable capabilities. Red areas are figures that we do not need in our final table. We only need areas colored in green. We could create a table similar to the one on the left using advanced excel formulas like Offset/indirect, but this approach would be time-consuming and would require some adjustments from time to time. You could assume that the table on the left side was created using DAX measures. Simple and efficient!
A great advantage of using Measures to input all needed facts in one PowerPivot table is that you can easily create Charts from table data, which are flexible and dynamic. So instead of a boring table like the one above, you can create a nice graph like in a picture below, which will adjust automatically when new periods arrive.
With measures, you can create lots of similar KPI tables and charts. The cool part is, you create measures once, and all you need to do is to refresh when new data arrives!
How to learn DAX?
First of all, I would highly recommend buying these two books.
First one is called PowerPivot and Power BI (by Rob Collie & Avichal Singh). This is a great book written in a friendly casual tone which will teach you the main concepts of DAX while showing you other aspects of PowerBI. You will get a clearer vision of what can be achieved with PowerBI and how. I would suggest you read this book from cover to cover. Some parts could seem complicated but the author manages to explain them in simple terms.
After reading the first book, if you are really interested in learning more advanced DAX and start implementing sophisticated data models based on DAX measures, you should get this book: The Definite Guide to DAX (by Marco Russo and Alberto Ferrari). Authors are the most authoritative persons in a field of DAX. When starting to study, be prepared to read it multiple times, with a PowerBI/PowerPivot installed and opened (since you will need to use practical examples to test and understand DAX). This book is written in a more formal way, teaching you all you need to know about DAX concepts, Hierarchies, relationships, VertiPaq engine, and code optimization. It will be hard to grasp some elements of DAX (like context transition and evaluation context) but with time and multiple reading through chapters, those ideas will become clearer and once they settle, they will stay in your memory.
Besides those two books, there are lots of useful blogs where you can always learn some new approach to formula solving.
What I would not recommend is to learn by searching the net for code examples, then trying to modify that code to suit your needs. For example, I like to google a lot when writing VBA code. There is no need to reinvent the wheel when somebody already created it (and trust me, most programmers do the same). I find a similar code on Web, adjust it to my needs, and implement it in my solution.
Do not take this approach when dealing with DAX. You will lose lots of your time and almost guaranteed will not find an appropriate solution. Every measure needs to be created considering the complexity of evaluation context and data models. Every data model is different, so measures written for one data model will not be even close to the needs of your data model. Take your time, learn by combining lectures, theory and practical work in PowerPivot, and results will follow!
Conclusion:
- With DAX you can create powerful complex models based on various sources. DAX is optimized for high-speed dynamic calculations, meaning your model will be fast even when working with millions of rows of data coming from different sources.
- DAX is a language designed for non-IT personnel, which brings BI implementation closer to the ones who understand business so that they could use DAX to bring increment to their everyday work.
- If you are an advanced Excel user already familiar with PivotTables/PowerPivot, you can use DAX to produce improved solutions based on data models connected with PowerPivot.
- To properly learn DAX, I would highly recommend reading the mentioned books thoroughly, to get a better understanding of the main concepts behind DAX. You can also find well-written articles and video conferences held by Alberto Ferrari/Marco Russo which are a great source of information for newcomers. Learning DAX by copying code found on the web and modifying it to your needs is longer and more painful way of learning DAX.
If you have any questions, please comment below, and if you liked this article, don’t forget to Share/Like!
