Getting Started With DAX In Power BI

The DAX (Data Analysis Expression) formula language is one of the key pillars in Power BI.

Learning DAX might be overwhelming at first. But once you understand how it works, you can level up your analytical insights.

Download Free Resources Here

The key to getting great insights in your reports is through the use of DAX calculations.

For this tutorial, I will discuss some important techniques that you need to remember when using DAX. This will not be a detailed discussion of DAX formulas and examples since there’s a separate course for that. I just want to help you in getting started with DAX by learning the essentials.

Table of Contents

Learning The Fundamentals of DAX In Power BI

Compared to other formula languages, DAX is the most efficient tool to use for generating high quality analyses. This is why it’s essential to know the fundamentals when using DAX inside Power BI.

You can write DAX formulas either through calculated columns or measures. You also need to understand what context is in Power BI.

You may have noticed the tutorials follow an order when discussing the different pillars of Power BI. We started with the basics of Query Editor, followed by the fundamentals of the Data Model. You need to understand these first before studying DAX calculations. This is because filters are always applied first before any calculation.

There are two calculation engines used for DAX in Power BI – the aggregators and iterators.

Aggregations are used to reduce your tables and data so you can improve the speed of your calculation. Meanwhile, iterations are used to evaluate logic at every row of your table. It’s essential to know the difference between these two functions so you can apply them in relevant scenarios.

Lastly, you need to be familiar with the most important function in DAX – the CALCULATE function. You’ll encounter this function a lot of times during your analyses and calculations. There’s no other DAX function in Power BI that can match the usefulness of the CALCULATE function.

Everything that I’ve mentioned here is the key insights about DAX that you should know before diving into any DAX calculations. In the next section, I’ll delve more into calculated columns and measures along with simple DAX calculations.

Using Calculated Columns In Power BI

As I have mentioned, you can write DAX calculations in Power BI by using either calculated columns or measures. First, I’ll show you how to use calculated columns.

In this example, you already have the Order Quantity, Unit Price, Total Unit Cost, and Total Revenue columns.

If you want to calculate your costs, you can calculate them using calculated columns.

To do that, click New Column from the Modeling tab.

Now that you have the new column, rename it to Costs. In the formula bar, you just have to multiply the Order Quantity by the Total Unit Cost.

After you press enter, you’ll see the corresponding results in the new column of your Sales table. That’s how simple it is to work with any calculation using calculated columns.

This technique works, but it’s unnecessary since you can just create a formula with an iterating function. If you use calculated columns, you’re also creating additional columns in your fact table.

But when you use iterators, you can calculate the same result without physically placing the data in your table. That’s the key difference between the two.

You should use calculated columns only when it makes sense to do so. That’s one of the key insights that I want to share with you.

Using Measures In Power BI

Now, I’ll show you how to use DAX calculations in Power BI through measures.

To do that, click New Measure from the Modeling ribbon to create a measure. You can also access and click New Measure from the Home ribbon.

Now, name the new measure as Total Sales. As I have mentioned a lot of times, make sure you name your measures properly.

Following an intuitive naming convention is important when organizing things in your Query Editor and Data Model. This is also important in DAX calculations too.

You have to name your measures according to what they are exactly about so that when other people use your analysis, they can easily understand what your data is about.

Going back to the example, you can start by writing simple measures. For instance, you can type a column that you want to reference, and then related suggestions will appear. Just press the tab to select the related suggestions, and press enter.

This is another reason why you need to name your columns properly. You can easily search for whatever you need when you use proper naming conventions.

In the example, you can find the Total Sales measure on the right-hand side of Power BI. You can now drag it into the table to see the Total Revenue column:

If you try to compare the data in the Total Revenue and Total Sales columns, you’ll see that the data is just the same. You can just delete the Total Revenue column here and use the new measure for total sales.

This example shows a simple DAX calculation using measures. You can also do other simple analyses like calculating total transactions, sales averages, and many more. Later on, you can branch out to more complex ones depending on your scenarios.

Additional Techniques For Using DAX

If you’re coming from an Excel background, you might be used to calculated columns. But here in Power BI, you should rely more on using measures for your DAX calculations.

Measures tend to work together with SUM or COUNTROWS functions while calculated columns work without supporting functions.

In this example, there’s already a column for Total Revenue in the table. You can even turn it into a visualization.

However, I don’t recommend this process. You should always write a DAX formula for any type of calculation instead of grabbing a column.

No matter how simple the data is, you need to create it using DAX measures. You’ll need it when you create more complex calculations using the measure branching technique.

Measure branching is the process of referencing certain measures within measures to create highly advanced analysis.

In another example, I used calculated columns to add additional dimensions to my table. The sample below calculates the product sales over time, but I didn’t use a time filter.

I used the data from Product Sales to create the Client Groups column through another calculated column.

Later on, I utilized Client Groups as a slicer in the visualization.

As I’ve said before, it’s better to do your calculations inside measures. Most importantly, never create a calculated column inside a fact table. To optimize your process, use measures instead so you can do calculations virtually.

For example, calculating Total Sales and physically bringing it into your table can take up additional memory in your model.

If you use measures, you can write hundreds of virtual calculations without taking up a lot of space or memory.