Power BI: Introduction To DAX

What is DAX?

DAX stands for D ata A nalysis e X pressions and is the language behind Power BI. It is also used in PowerPivot and SQL Server Analysis Services.

Microsoft built DAX to manipulate data within some of its applications and it is a very powerful language.

At its simplest level, DAX is used for 2 things; calculated columns and calculated measures. If you are familiar with pivot tables in Excel, it works in a very similar way to calculated fields.

Calculated columns are columns that can be added to your data to enrich it.

For example, you may want to know what your profit is per line item of a sale. Your calculated column could be created as Profit = [Sale Price] – [Cost]. This formula would generate a Profit value for each row in your data and allow you to summarize it to any level you choose when visualizing it.

Now, what about if you wanted to calculate a profit percent? This is better calculated as a measure as you may want to aggregate it based on product category, or time period, or some other category. The process is very similar for a measure, the only real difference is how it is calculated and the context.

A calculated column is evaluated for each row of data. A measure is evaluated based on the context of the reporting level you are viewing. As you summarize the data more, a measure will automatically scale with your context, you will have to choose how best to summarize your column. All of this might seem a little complicated, but I assure you, once you get your hands on it, it will make a lot of sense.

You may be saying to yourself, that’s great and all, but Excel can do all of that, why bother with Power BI?

For starters, the functions available in Excel pale in comparison to the suite of functions in Power BI for calculated columns and measures.

Below is a list of functions available in Power BI, some will be familiar if you have used Excel.

1)Date and Time functions

Similar to Excel date and time functions and allow you to convert or calculate dates and times.

2) Time-intelligence functions

Using built-in knowledge of calendars and dates, you can create calculations to help compare data across time periods. One example is year-over-year comparisons.

3) Filter functions

As the name suggests, these functions help filter and retrieve specific data. You can then create different views of your data dynamically.

4) Information functions

These functions are logical tests that in most cases will return a TRUE/FALSE response. Items such as ISBLANK or ISNUMBER.

5) Logical functions

These are functions are your operators in your expressions such as AND, IF, OR.

6) Math and Trig functions

Very similar to Excel, these will help you to solve specific maths questions.

7) Parent and Child functions

The functions allow users to manage data that has a parent/child structure.

8) Statistical functions

These are functions designed for aggregation purposes such as sums and averages at the simple end and for measuring and calculating statistical values like standard deviation at the more complicated end.

9) Text functions

These functions are designed to manipulate strings such as concatenating or parsing text.

These are the main categories of functions. If you would like to understand what is available in more detail, Microsoft has a reference guide.

Why is DAX important

DAX provides you the ability to manipulate data in many ways without having to create a new dataset altogether. This can be a huge differentiator compared with Excel.

In Excel, you will encounter situations where you need to slice data multiple ways and create different pivot tables to be able to see things in all the different ways you need. The beauty of DAX is that it allows you to have a single dataset that you enhance with calculations.

Now if all DAX could do was these simple calculations listed above, that would still be beneficial, but it can do so much more.

DAX Learning Curve

The simple formulas will seem no different than using Excel and you will be able to pick that up in no time with help from the guided formula builder in Power BI.

However, as you become more familiar with the nuances of DAX you will likely start to test the waters with some advanced formulas.

Advanced DAX

DAX allows you to build complicated formulas to manipulate and aggregate your data. If you are familiar with writing code, or even complicated formulas in Excel, then this will be very similar, just more complicated. Since DAX has its own syntax, it will take some time to get comfortable with the more advanced features.

DAX enables you to do many of the transformations that previously would have had to be done in a database with advanced queries and logic. It pushes the power into the hands of the report builders and analysts as opposed to having the bottleneck with the data engineers or DBA’s. This allows for the democratization of data to spread as users are empowered to build the kinds of tools they need, and they don’t need to wait for the few technical resources to have sufficient time to get to their request.

DAX Use Case

Imagine you are a sales organization that has a physical store with sales employees.

In order to motivate your employees you have decided to rollout gamification and have scoreboards showing who was the top salesperson on any given day, week, month, or some other time period.

The way you may have handled this in the past is to take your sales data and aggregate it to all of the different time periods that are relevant to you. This means maintaining multiple queries. This is a pain if you do not have access to a database or the ability to write queries like that, you may not have a solution other than brute force.

This is where DAX comes in handy. You can create a single formula for each of the time periods you want to have rankings for, and Power BI will dynamically rank your staff based on the criteria you have provided.

In this situation, all you would need is the raw sales transaction data, and your report developer or analyst can build the logic once for you and you’re set up. Because the logic is built right where you can visualize it and test it, it can be a much quicker and more reliable process.