In my post on Overview of DAX the previous week, I stated that there are mainly four use cases of DAX in Power BI:
Since the four use cases are all for different purposes, let’s begin with what is common to them all.
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.
I am sure you do not like errors. So, you will not try to break those rules.
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.
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?
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:
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:
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.