Power BI Calculated Tables: Key Insights and Tips

Are you looking to supercharge your data modeling in Power BI? We have Power BI calculated tables – a data analytics and visualization game-changer.

Let’s say you have multiple data sources. You need to create a unified, comprehensive table for analysis and reporting. That’s where Power BI calculated tables come in. They allow you to craft virtual tables tailored to your needs by leveraging the might of Data Analysis Expressions (DAX).

Power BI calculated tables are not just another feature. They are the key to unlocking deeper insights and accelerating your data analysis process. They are so powerful you can use them for visualization purposes in your Power BI dashboards. This introduces an additional element to your data narrative.

In the fast-moving business intelligence industry, quickness and precision are crucial. Using Power BI calculated tables enables you to efficiently merge and adjust data while preserving a strong data structure. Therefore, you can effectively walk your way through complex data situations. This allows you to concentrate on the most important things””making informed, data-based choices.

Are you ready to elevate your data modeling skills? Come and participate in the discussion on Power BI calculated tables with us. You’ll learn how this feature transforms data analysis and reporting.

Table of Contents:

1. What are Calculated Tables in Power BI?
2. Why are Power BI Calculated Tables Important?
3. When to Use Calculated Tables in Power BI?
4. How Calculated Tables Work in Power BI?
5. How to Create a Calculated Table in Power BI?
6. How to Create Visualization in Power BI?
7. What are the Benefits and Limitations of Calculated Tables in Power BI?
8. Wrap Up

First…

What are Calculated Tables in Power BI?

Definition: Calculated tables in Power BI are virtual tables created using Data Analysis Expressions (DAX). They define the table’s values based on existing data in the model. They are beneficial for intermediate calculations and storing data as part of the model.

Calculated tables allow you to combine and manipulate data without querying and loading values for each new table. They can be used for:

• Role-playing dimensions
• Creating relationships with other tables
• Adding measures and calculated columns for visualization purposes

Calculated tables offer a quick and efficient method to enhance data modeling and analysis in Power BI. Functions like DISTINCT, VALUES, CROSSJOIN, UNION, and others can be used to define these tables. They provide immense flexibility in creating formulas for various data analysis needs.

Why are Power BI Calculated Tables Important?

Power BI calculated tables are important for several reasons:

• Data modeling flexibility: Calculated tables provide the flexibility to create new tables based on existing data in the model. This allows for advanced data modeling capabilities.
• Custom aggregations: Calculated tables allow for custom aggregations and intermediate calculations. They also store pre-calculated data as part of the model.
• Intermediate calculations: They are useful for intermediate calculations and storing data as part of the model. You don’t calculate on the fly or as query results, enhancing the analytical capabilities of the model.
• Enhanced relationships: Calculated tables enable the creation of new tables based on data already loaded into the model. This enhances relationships with other tables and allows for advanced data analysis.
• Dynamic data: You can recalculate calculated tables when any tables from which they pull data are refreshed or updated. This ensures the model reflects the most current data.

When to Use Calculated Tables in Power BI?

Calculated tables in Power BI are useful for various purposes. Here are some scenarios where you can use calculated tables:

• Complex data transformations: Calculated tables are ideal for performing complex data transformations. How? By using the DAX format function to create new tables based on DAX expressions derived from other tables within the same model.
• Intermediate calculations: Calculated tables are recommended for intermediate calculations. You can store the calculated data as part of the model rather than calculating it on the fly or per query result. This can help improve the efficiency of data analysis processes.
• Creating summary tables: You can use calculated tables to create summary tables based on the data already loaded into the model. Creating new tables without needing to query and load values from an external source enhances data visualization and analysis.
• Establishing relationships: Calculated tables can exhibit relationships with other tables, providing a way to establish connections and dependencies within the data model. This can streamline the data analysis process. How? By ensuring the calculated tables are recalculated when the source tables are refreshed or updated.
• Dynamic data models: You can use calculated tables to create dynamic models that adapt to changing data requirements or business scenarios. By leveraging DAX expressions, you can define the tables’ values and adapt the data model to evolving analytical needs.
• Scenario analysis: Calculated tables are valuable for scenario analysis. You can employ different DAX expressions to create tables representing various scenarios or what-if analyses within the model.

How Calculated Tables Work in Power BI?

You can create new tables in calculated tables using the existing data in the model. You don’t have to retrieve and insert values from a data source into the columns of the new table. Instead, you can generate a DAX formula to determine the table’s values.

It is advisable to use calculated tables for intermediate calculations. Also, use data that you can store as part of the model. There is no need to calculate them on the spot or for each query result.

Calculated table columns come in various data types and formats and may also include a data category. Similar to different tables, they can be tagged and incorporated into the process of documenting visualizations. If the tables they gather data from are updated, the calculations are also updated. This makes the data analysis process more efficient.

How to Create a Calculated Table in Power BI?

Here is a step-by-step creation and Functions for calculated tables:

Step 1: Navigate to the Data View section within Power BI Desktop. The data view is displayed in a grid layout located on the left side of the Power BI Desktop.

Step 2:  Click the Table tools option located at the top right corner.

Step 3: Click New table in the upper right corner. A dialog box will appear for creating a calculated table using DAX.

union_data = UNION(Sales_data_2019,Sales_data_2020)

Input this DAX code into the open dialog box to create the calculated table. We are merging the two physical tables into one by employing the UNION operation. This code will produce the following result:

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

Filter

It is employed to filter the Physical table based on the provided condition.

DAX code for Filter:

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

We are trying to generate a calculated table by applying the FILTER condition to the “overall_profit_of_year” column located in the “Sales_data_2019” physical table. We are trying to retrieve information from Sales_data_2019, where the total profit for the year is >=30. The result of the code will be:

Distinct

The name suggests that it generates a structured chart containing unique values.

DAX Code for Distinct:

Distinct_example = DISTINCT(Sales_data_2020[overall_profit_of_year])

This code will produce the following result:

Values 

Both Values and Distinct function similarly, producing unique values as output. The only distinction is that Values includes a blank row in the output, whereas Distinct does not. For better understanding, refer to the Filter_example table (which is the previously generated table for the filter function). I wanted to fetch the overall_profit_of_year column using the Distinct and Values functions to compare the differences between them.

DAX Code for Values:

value_example = VALUES(Filter_example[overall_profit_of_year])

The result of the code mentioned above will be:

Let’s use the Distinct function on the Filter_example table.

DAX Code for Distinct:

distinct_example = DISTINCT(Filter_example[overall_projit_of_year])

The result for the code above will be:

In the Values_example output, you can identify an empty row that is not present in the distinct_example output. We must thoroughly explore the concept to understand it. First and foremost, verify if the Do Filter_example table shows any connections with other tables. If this is the case, the Values function operates based on the tables’ relationship, while the Distinct function remains unaffected by table relationships.

The Filter_example table has numerous connections with the union_data table, resulting in a blank row in the value_example table. This indicates a unique profit value in the union_data table that is missing from the Filter_example table.

For a clearer understanding, compare the profit column for the filter_example and union_data tables in a matrix. Notice that in the union_data representation, hovering over 13% profit of year with id 4 is not found in filter_data, but id 4 is present. When the Values function is used, we receive a row with no data.

CALCULATETABLE

Evaluates a table expression in a modified filter context.

Syntax: CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, ”¦]]])

Term	Definition
expression	The table expression to be evaluated.
filter1, filter2,”¦	(Optional) Boolean expressions or table expressions that define filters or filter modifier functions.

The first parameter must be a model table or a function that generates a table.

Types of filters include:

• Boolean filter expressions
• Table filter expressions
• Filter modification functions

If there are many filters, they are assessed by applying the AND logical operator. This implies that every condition has to be TRUE simultaneously.

Example

In this instance, the CALCULATETABLE function is utilized to calculate the total Internet sales for the year 2006. This value is subsequently utilized to calculate the ratio of Internet sales compared to all sales for the year 2006.

Look at the formula below:

= SUMX(

CALCULATETABLE(

‘InternetSales_USD’,

‘DateTime'[CalendarYear] = 2006

),

[SalesAmount_USD]

)

It results in the following table:

Row Labels	Internet SalesAmount_USD	CalculateTable 2006 Internet Sales	Internet Sales to 2006 ratio
2005	$2,627,031.40	$5,681,440.58	0.46
2006	$5,681,440.58	$5,681,440.58	1.00
2007	$8,705,066.67	$5,681,440.58	1.53
2008	$9,041,288.80	$5,681,440.58	1.59
Grand Total	$26,054,827.45	$5,681,440.58	4.59

How to Create Visualization in Power BI?

Here is a step-by-step process for creating a visualization in Power BI.

Stage 1: Logging in to Power BI

• Log in to Power BI.
• Enter your email address and click the “Submit” button.

• You are redirected to your Microsoft account.
• Enter your password and click “Sign in“.

• You can choose whether to stay signed in.

• Once done, the Power BI home screen will open.

Stage 2: Creating a Data Set and Selecting the Data Set to Use in Your Chart

• Go to the left-side menu and click the “Create” button.
• Select “Paste or manually enter data“.

• We’ll use the HR sample data below for this example.

Application Channels	Initial Screening	Conduct Interviews	Employee Onboarding	Total Candidates
Social Media	Short Listed	Final Interview	Hired	32
Social Media	Short Listed	Final Interview	Not Hired	400
Social Media	Short Listed	Knocked Out		800
Social Media	Knocked Out			1100
Company Career Page	Short Listed	Final Interview	Hired	20
Company Career Page	Short Listed	Final Interview	Not Hired	250
Company Career Page	Short Listed	Knocked Out		500
Company Career Page	Knocked Out			900
Events	Short Listed	Final Interview	Hired	5
Events	Short Listed	Final Interview	Not Hired	100
Events	Short Listed	Knocked Out		200
Events	Knocked Out			350
Paper Media	Short Listed	Final Interview	Hired	3
Paper Media	Short Listed	Final Interview	Not Hired	80
Paper Media	Short Listed	Knocked Out		135
Paper Media	Knocked Out			700
Employee Referrals	Short Listed	Final Interview	Hired	10
Employee Referrals	Short Listed	Final Interview	Not Hired	70
Employee Referrals	Short Listed	Knocked Out		80
Employee Referrals	Knocked Out			110
Direct	Short Listed	Final Interview	Hired	25
Direct	Short Listed	Final Interview	Not Hired	150
Direct	Short Listed	Knocked Out		425
Direct	Knocked Out			600

• Paste the above data table in the Power Query Window.
• Select the “Create a dataset only” option.

• On the left-side menu, click “Data Hub“.
• Power BI populates the data set list. (If you have not created a data set, refer to the Error! Reference source not found section).

• Click on the “Create a report” dropdown.

Stage 3: Adding the Power BI Sankey Diagram Extension by ChartExpo

• Creating the Sankey Diagram requires us to use an add-in or Power BI visual from AppSource.
• Navigate to the right side of the Power BI dashboard.
• Open the Power BI Visualizations panel.
• Click the ellipsis symbol (…) as highlighted in the diagram below. This will import the Power BI Sankey Diagram extension by ChartExpo.
• The following menu opens:
• Select the “Get more visuals” option.

• The following window opens.
• Enter “Sankey Diagram for Power BI by ChartExpo” in the highlighted search box.
• You should see the “Sankey Diagram for Power BI by ChartExpo”, as shown in the image below.

• Click the Sankey Diagram option and click the “Add” button.

• You can now see the Sankey Diagram for Power BI by the ChartExpo icon in the visualizations list.

Stage 4: Drawing a Sankey Diagram with ChartExpo’s Power BI extension

• Select the “Sankey Diagram for Power BI by ChartExpo” icon in the visualization panel.
• The following window opens in the report section of your dashboard:

• Select the fields to use in your Sankey chart here.

• One thing is important when you select the fields: you must follow the sequence below:
  • Application Channels
  • Initial Screening
  • Conduct Interviews
  • Employee Onboarding
  • Total Candidates
• You’ll be asked for a ChartExpo license key or email address.

• Add the key under the Visual section.

• After adding the key, you can see a chart for your data.
• You can add the top header text in the chart under the General section as follows:

• After that, you can click on Visual set, the number, and enable the options below:

• You can change the “Node Font Style” as follows:

• You can change the “Level Font Style” as follows:

• You can see the Level Labels as follows:

• You can change the Node color by following the steps below:

• Now we show you “How to change Level 1 color,” and then by following this, you can change the node color of other levels as follows:

• Below is the final look of the HR Dashboard in Power BI using ChartExpo after changing the nodes’ color.

Insights

• Social Media is the leading platform for applications, having the largest pool of candidates (1100).
• The Company Career Page follows with 900 candidates.
• Events and Paper Media have fewer candidates, with 350 and 700, respectively.
• 600 individuals submitted their applications directly.
• Employee Referrals consist of 110 possible candidates.
• Social media applicants outnumber other candidates on the shortlist (32 selected, 400 not selected).
• The Company Career Page and Direct applications attract a high volume of qualified candidates.
• Paper Media and Events receive a smaller number of chosen submissions.
• Knocked-out candidates are most commonly found on social media platforms (800), with the Company Career Page (500) being the second most popular location.
• Employee recommendations and applications submitted directly have lower rates of rejection.

What are the Benefits and Limitations of Calculated Tables in Power BI?

Calculated tables in Power BI offer several benefits:

• Flexibility: They provide flexibility in storing intermediate results and creating desired table structures.
• Custom aggregations: Calculated tables allow for custom aggregations and unique values from multiple tables.
• Simplified DAX formulas: They simplify the DAX expressions, making working with complex data models and queries easier.
• Enhanced relationships: Calculated tables create and maintain enhanced relationships within the data model.

However, calculated tables also have limitations:

• Performance impact: They can lead to slower data processing time and increased processing time during the development of large models.
• Data refresh complexity: The Process Recalc happens every time a full process is done, leading to increased data refresh complexity.
• Storage overhead: Calculated tables may not be optimized for storage compared to regular tables, which could affect query performance.
• Data model complexity: They can contribute to increased data model complexity. Especially when used extensively without proper consideration of performance implications.

FAQs

How do I add calculations to a table in Power BI?

To add calculations to a table in Power BI, create calculated columns using DAX. This will help you define new columns based on expressions evaluated row by row. Additionally, you can create custom columns using the Power Query M formula language.

What is the difference between a measure and a calculated table in Power BI?

A measure is a DAX expression that provides aggregations, calculations, or business logic and is used in visualizations. A calculated table, however, is a table created by a DAX expression. This table can be used in the data model to define table values based on existing data.

How do you calculate values from different tables in Power BI?

To calculate values from different tables in Power BI, use DAX expressions. Assume you have a relationship between Table A and Table B. You can create a calculated column in Table A using the RELATED function to fetch values from Table B.

Wrap Up

Power BI calculated tables are vital for advanced data modeling and analysis. They provide flexibility by allowing you to create new tables based on DAX expressions from existing data. This facilitates complex data transformations and intermediate calculations.

Additionally, calculated tables simplify the definition of table values using DAX formulas. This feature offers over 200 functions, operators, and constructs for various data analysis needs. This simplification enhances the ability to create complex data models and scenario analyses and establish relationships with other tables. As a result, it improves data modeling efficiency.

Moreover, calculated tables offer custom aggregations and dynamic data model capabilities. They allow for scenario analysis and adaptation to changing business requirements.

However, it is important to recognize calculated tables’ limitations. They can impact performance, data refresh complexity, storage overhead, and data model complexity, especially when dealing with large datasets. These limitations necessitate careful consideration and optimization of calculated tables to maintain Power BI’s model performance and manageability.

In conclusion, calculated tables in Power BI are important because they provide flexibility. They also simplify data modeling and enhance the efficiency of data analysis. Leveraging calculated tables allows you to perform advanced data transformations, create summary tables, and establish relationships.

While mindful of their limitations, calculated tables remain an indispensable tool for generating insights. From this, you can make informed decisions and drive business growth through data-driven strategies in Power BI.