Practical Introduction to DAX ( Intro to Calculated Tables)

What you should Know before we begin

In my post on Overview of DAX the previous week, I stated that there are mainly four use cases of DAX in Power BI:

1. Creating New Tables (Calculated Tables)
2. Creating New Columns (Calculated Columns)
3. Writing Dynamic Formulas (Measures)
4. Defining Data Access Level Controls (Row Level Security).

Since the four use cases are all for different purposes, let’s begin with what is common to them all.

Names:

Every single DAX formula you write must have a name. You will always provide the name(s) for new table(s) you create, for new column(s) you create, for new measure(s) you write and for every role you define.

That is simple enough. However, there are things to note about names.

Caution with Names

1. You cannot use a reserved name as the name of your DAX code. For example, you cannot name your Column “SUM” because SUM is a reserved name for a function in DAX
2. You cannot use the same name for two Measures, neither can you use the name of an existing column in your model as your Measure name. So, if you have a column named “Sales”, you cannot create a Measure named “Sales”.
3. You cannot use the same name for two columns on the same table.
4. You cannot use the same name for two tables in your model.

I am sure you do not like errors. So, you will not try to break those rules.

Practical Introduction to Calculated Tables

This week, I’ll take you through a soft practical introduction to Calculated Tables. We are going to thread gently. Baby steps.

The remainder of this post now will be demos. You can download the Excel data file here for your practice.

I cannot guarantee that my examples will make real life sense, please bear with me. Kindly focus on the concepts I will describe.

Creating Calculated Tables in Power BI

If for any reason, you need to derive a new table in your model, then you will need DAX. One of such tables we always need in our models are Date Tables. Zainab Balogun has written a four-part series on how to create date tables in Power BI. Three of which highlight DAX methods. You should check out the posts.

Here, I’ll show you another quick and simple example of a Calculated Table.

But firstly, how do you create a calculated table with DAX?

How to create a new Table with DAX.

1. If you are in Report View on Power BI, click on Modeling Tab, then click on New Table. Or, If you find yourself in the Data View though, Click on Table Tools Tab and select New Table.

2. Change the default name “Table” to the name of the table you want to create. Then write your DAX code after the “=” sign.

3. Remember to hit the Enter key on your keyboard afterwards.

As a simple example for demo purposes, I have a single table in my model containing sales data for a super store. One of the fields on the table is “Sales Rep”, recording the source of each sale. If I want to see only the sales coming from the Organic Sales Rep, I could just:

1. Go to the Data View

2. Click on Sales Rep Field from the Fields Pane.

3. Click on the Drop down on Sales Rep and filter to show only “organic”.

That works fine. However, what if I want the result as a separate table?

So, the New Table should have only sales data for the Organic Sales Rep. Anew table is called a Calculated table isn’t it? And it needs DAX to get it created. What I want my DAX to do is:

1. Create a new table that has the Sales Data
2. But the Sales data should be filtered on the Sales Rep Column to have only values for “Organic”.

To achieve these, I’ll follow the steps on How to create a new table with DAX.

1. From the Data View, click on Table Tools and Select New Table

2. Change the name from “Table” to “Organic Sales Data”

3. To create a new table that has the sales data, I’ll type SalesData after the “=” sign and select the SalesData table from the intellisense option. If I hit the Enter key on my keyboard, this will give me a replica of the SalesData table with all columns and all rows. This is not what I want.

4. I’ll need a function in DAX that can help me filter tables. That’s exactly what the FILTER function does. The function has two arguments or requires two inputs:

1. 1. 1. Table: The name of the table to be filtered. In this case, the SalesData table
      2. Expression: The definition of the filter to be applied on the table.The SalesRep column must be “Organic”. So in this case, SalesData[Sales Rep] = “Organic”I’ll now need to modify my formula to the filtered version of SalesData using the formula:  FILTER(SalesData, SalesData[Sales Rep] = “Organic”)

Conclusion

Now, I have a new table, containing only the SalesData for “Organic” sales only. Of course, it doesn’t make a lot of real life sense doing something like this. What makes sense, is that you understand the task I tried to create for a Calculated Table, you understand the process I took to create it and the formula I used to execute.

Asides Date Tables, creating Calculated Tables is not the most popular thing we use DAX for. In my next post, I will give another gentle introduction, this time on Calculated Columns.

Ensure you practice this. You learn DAX by practicing.