Power BI - How to Create Calculated Tables?

A calculated table can be defined as a Virtual Table that is created by using a Physical Table through DAX (Data Analytic Expression). A physical Table is generally an imported table from external data sources, but this calculated table is created according to our need for a physical table by using DAX. Like physical table columns, calculated table columns also have a data type, formatting options, and many more. We can also change the names of the calculated table and calculated columns according to our use. We can use these calculated tables for visualization purposes also in our Power Bi Dashboards. Like Physical Tables, these Calculated Tables also exhibit a relationship with the other Tables.

Steps to Create Calculated Tables

Suppose, If the manager asked you to collect the profit information of sales for 2019,2020 in one table. But we have two physical tables which contain profit information for 2019 and 2020. You can access this data through the below links. This is a small data of an Indian store named super_mart  which contains overall_profit information for the year 2019,2020.

• Sales_data_2019
• Sales_data_2020

For this, we need to create a Calculated table by combining these two tables into one.

Step 1: Go to Data View in Power Bi Desktop. (Data view is represented in a grid format which is present on the left side of the Power Bi Desktop)

Step 2: Click on Table tools present on the upper right-hand side.

Step 3: Click on New table, [you can see it on the upper right-hand side], A dialog box is opened for creating a calculated table through DAX.

DAX Code to Combine Two Tables

union_data = UNION(Sales_data_2019,Sales_data_2020)

Enter the above DAX code in the Dialog box opened for creating the calculated Table. Here we are combing the two physical tables into one by using the UNION operation. The output for the above code will be:

Not only UNION, but we also have many more table functions for creating calculated tables:

Filter

It is used to filter the Physical table according to the condition given.

Syntax: FILTER(Table, Filter expression)

DAX Code for Filter:

Filter_example = FILTER(Sales_data_2019,Sales_data_219[over_all_profit_of_year]>="30")

Here we are trying to create a calculated table by using the FILTER condition on the "overall_profit_of_year" column which is in the "Sales_data_2019" physical table. We are trying to fetch the data from Sales_data_2019 that contains overall_profit_of_year >=30. The output for the above code will be:

Distinct

The name itself indicates that it creates a calculated table with distinct values.

Syntax: DISTINCT(ColumnName Or Tableexpression)

Note: If column name is given as a parameter it gives distinct(unique) values as output in the selected column, if table expression is given as a parameter it gives a distinct combination of values as an output in the selected table.

DAX Code for Distinct: 

Distinct_example = DISTINCT(Sales_data_2020[overall_profit_of_year])

The output for the above code will be:

Values 

Both Values and Distinct behave in the same way by providing unique or distinct values as output but the only difference is Values include a blank row also in the output where as Distinct will not. For better understanding consider the Filter_example table (This table is the above created calculated table for filter function). So Here I wanted to retrieve the overall_profit_of_year column by using the Distinct and Values functions So you can observe the difference between these functions.

Syntax: VALUES(Tablename or columnname)

DAX Code for Values:

value_example = VALUES(Filter_example[overall_profit_of_year])

The output for the above code will be:

Let's apply the Distinct function for the Filter_example table,

DAX Code for Distinct:

distinct_example = DISTINCT(Filter_example[overall_projit_of_year])

The output for the above code will be:

You can see the empty row in Values_example output but there is no empty row in distinct_example output. We need to dive deep into the concept to understand this. Firstly, check  Do Filter_example table exhibits a relationship with any other tables. If so the Values function behaves according to the relation between the tables, but the Distinct function is not affected by the relationship between tables. 

If you can observe the above image Filter_example table exhibit too many relationships with the union_data table so we are getting an empty row in the value_example table, which is telling us that we have a distinct profit value that is present in the union_data table but not in Filter_example table.

To understand it better I visualized the overall profit column for the filter_example table and union_data table in the matrix, if you can see the mouse has hovered over 13% (overall_profit_of_year) with id 4 in union_data_overallprofit representation which is not present in the filter_data_overall_profit but we have id 4. So we are getting an empty row while we are using the Values function.

Note: If there is no relationship between tables Distinct and Values functions behaves as same by providing distinct or unique values as output