In this article, my main intention is to explain the Importance and Significance of DAX. I would like to focus on the points mentioned below –
- Why should you Learn DAX ( Worth your Time Investment )
- Where can you write DAX ( in Excel & Power Bi )
- And why should you deprive yourself from this feature
If you are an Excel User, you can unleash the Power of Pivot Tables, using DAX.
Dax stands for Data Analysis Expression, if I have to explain in simple, DAX is a formula language written specifically to a Pivot Table.
The entire article is explained in the form of a video below:
Suppose, if I ask you a question, how important are Pivot Tables, in Excel, we would mutually agree, it’s one of the most powerful, important & easy options, to create Data Analysis. Now, just imagine, if you can start writing a formula specially for a Pivot Table, thats what DAX is about.
Yes, you need to integrate DAX with Power Pivot, but Power Pivot is again an extension of Pivot Table. In this article, let’s take a simple example, regarding DAX. A very common function which is used in every corporate company ( Year To Date ) – YTD.
Not that, you can not calculate YTD in Excel, you can, if you google it ( YouTube 🙂 ) you will a number of video’s where you can get instructions to calculate, but they are not simple, and most important, if any changes is asked to you at the last moment, for example: Filter the YTD for a specific product / category / sub category, in that case your entire calculation would crumble.
Solution – DAX.
We have a simple formula in DAX, which is TotalYTD. Take a look at this practical example.
This is assuming, that we have already added these tables to the Data Model in Power Pivot: We have a detailed Video regarding, how to add your Data to the Data Model, if you want to follow along, we will also have the link to the file used below
The products & sales tables have been added to the Data Model, and we create the relationship’s
Here, we have defined the relationship’s between the products & sales tables.
if this would have been done, without Power Pivot, we would have to do a VLOOKUP between two sheets.
Added, the DATE table to our Date Model
Connected the Order Date from the Date Table ( Many Side ), with the Full-Date-Alternate-Key from the date table, which is the 1 Side.
We add a measure in Power Pivot, a measure is something
To simplify it for you , we need to write this in DAX
“There are 200 DAX functions, as per the Microsoft Documentation”
The DAX formula, which we have used is “SAME-PERIOD-LAST-YEAR”
Just imagine, just 1 line, of code, and this code is specifically only for a PIVOT TABLE.
You can see the figures for 2006-July, in the 2nd Column you can see the sales for the current year, and in the 3rd column, you can see the sales for the previous year in the same column, this is a simple example regarding DAX. what what you are seeing are here are formula’s specially written only for a Pivot Table.
Here, we have added the column, to show us the difference of Sales, between the current year and the previous year, next we could have simply plotted a visual, where we could subtract the current year sales
We add slicer’s to this data, now just imagine if the same thing is done without DAX. It’s not that we can’t do, but the same results couldn’t be achieved under 15minutes of time & given a slicer, your output would have not changed.
Let’s take the same Example in Power BI.. we have the same data model, opened in Power BI. Set up the relationship’s. Remember
“Dax Uses Relationship’s”
The same code, which was used for DAX in Excel, has been used for Power Bi. This was just to show, that the same code can be used in both the platform’s.
There are certain DAX codes, which only work in Power BI, and not in Excel, but still it's always worth the time and investment.
