Sign in or start a free trial to avail of this feature.
3. Creating Calculated Tables
You can add a new table to the data model in Power BI using DAX formulas. In this lesson, we’ll create a new table and create a relationship with the existing table.
Understanding Calculated Tables (00:11)
A calculated table is a new table added to a data model using DAX formulas. Our existing data model contains sales information for pharmaceutical products, so we want to create a table containing details about those products.
Lesson Goal (00:24)
The goal of this lesson is to create a calculated table and create relationships between it and the rest of the data model.
Creating a New Table (00:38)
It’s best to create new tables in data view, so we can see the table being built. To create a table, we select New Table from the Modeling tab. We then enter the DAX formula that creates the first column of the table. In our case, we use the DISTINCT function to identify all the product names that appear in the existing table, without identifying duplicates.
The name at the start of the formula defines the name of the table, not the name of the first column. After we’ve created the first column, we can then add more columns to the table in the same way as with any other table.
Creating Relationships With Existing Tables (02:06)
When we create a new table, it does not have any relationships with existing tables. We create these relationships in modeling view, by dragging between the relevant fields in the new table and the existing table. In our case, we create a many-to-one relationship between the product names in the two tables of the data model.
Over the past two lessons, we've covered how to create calculations with measures and calculated columns.
Let's take a look at the third calculation option, calculated tables.
A calculated table is a new table you add to your data model using DAX formulas. Our current data set details sales transactions of various pharmaceutical products. We would like to add a new table that contains information about each of the products. In this lesson, we'll created a calculated table containing the name of each product, along with the relationship connecting it to the data model.
We'll start by entering data view.
We'll then navigate to the modeling tab, and select new table.
As with columns, it's best practice to create tables in data view, so we can actually see the table as we add it.
As you can see, our new table contains a single empty column We'll now enter a formula to create the first column in the new table.
The first column should contain a list of products that feature in our other table.
To that end, we'll enter the formula, product details...
Power BI Pharma Data, product name.
Note that when creating a calculated table, the name at the start of the formula will be the name of the table, not the name of the column. The distinct function identifies the unique values from the specified column. In this case, the Product Name column.
We'll discuss this function in further detail in a later lesson.
We'll now press enter, and see that this formula has returned all of the unique product names from our Pharma Data table.
Note that the column has been given the title Product Name because the data was taken from the Product Name column in the other table. We'll add more data to this table throughout the rest of this course. But for now, let's move to relationship view.
When we create a calculated table, it won't automatically create relationships with the existing tables. However, there should be a relationship between our two tables, as the same product names feature on both. We'll create the relationship by dragging Product Name in the Product Details table to Product Name in the Pharma Data table.
If we look closely at the relationship, we can see that the line joining the tables has an asterisk next to the Pharma Data table, and a one next to the Product Details table.
In a relationship, the asterisk means many, so this is a many-to-one relationship.
In this relationship, each product appears only once in the Product Details table, but appears many times in the Pharma Data table.
We'll stop the lesson here.
Over the past few lessons, we've seen the three different types of calculations that can be performed in Power BI.
In the next lesson, we'll build on this foundation, and start looking at some of the most commonly used DAX formulas.