Power BI Calculated Table

What is a Power BI Calculated Table?

Power BI calculated table represents a user-defined table that’s created by specifying a DAX (Data Analysis Expressions) formula that defines its contents. Power BI calculated tables allow you to add new tables based on the dataset already loaded to your model in Power BI. These tables are different from regular data tables in that they don’t store data directly; instead, they generate a table based on the formula you provide. These tables can be helpful for creating custom data views, aggregations, and other data transformations within your Power BI model.

Power BI calculated tables can have data relationships like other regular tables and hence have data types and similar other relevant properties. When your datasets are refreshed, it also triggers recalculations on the Power BI calculated tables.

Syntax

To create a calculated table in Power BI, you use the following DAX formula syntax:

Where

Table – refers to the table expression that needs to be evaluated

Filter 1 – refers to the Boolean expressions or table expressions that require to be defined for filters or filter modifier functions. This can be any Boolean filter expressions, any table filter expressions, or even filter modification expressions

In case you have defined multiple filters, then evaluation will be performed based on the logical operators in excel such as AND, and all your conditions must be TRUE.

Filter Expressions	Description
Boolean Expressions	These expressions evaluate either TRUE or FALSE. Such expressions must ensure Such expressions can’t reference measures • They refer to a single-column • Such expressions can’t reference measures • Using a nested CALCULATE function is prohibited • Using a function that returns a table is not allowed unless they are passed as an argument to any aggregated functions. • Using an aggregated function is allowed
Table Expressions	• These expressions can use any table object as a filter which can be a reference to a model table • Use the FILTER function to apply any complex filter conditions
Filter Modifier Functions	These functions provide you the flexibility to add filters and control over modifying any context.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to create a Calculated Table in Power BI?

To create a Power BI calculated table, follow the steps highlighted below:

Step 1: Open your installed Power BI Desktop and load your data source.

Step 2: Navigate to the Fields pane and select the data table that you have loaded

Step 3: Navigate to the Table tools and then select New table.

Step 4: Enter a name for your calculated table, and follow the syntax of the DAX expression

Step 5: In the formula bar at the top, input your DAX expression to define the table’s content.

Step 6: Press Enter to create the calculated table.

Note: You can also create a Power BI calculated table based on slicer by using DAX expressions such as SELECTCOLUMNS or FILTER functions. Similarly, you can also create a Power BI calculated table using the Group By function. It is helpful in summarizing the data and grouping it to store in a new table.

Examples

In this section, we will look at some of the examples of creating Power BI calculated tables.

Example #1 – Using Power BI calculated table from multiple tables

In this example, we will create a Power BI calculated table from multiple tables using the Youth and Adult Literacy Rate around the Globe dataset. The Youth and Adult Literacy Rate around the Globe dataset contains

• Adults_15YrsAndUp – Contains literacy rate information for Adults
• Youth_15to24Yrs – Contains literacy rate information for Youth

To create a Power BI calculated table from multiple tables, follow the steps below:

Step 1: Open Power BI Desktop and import the Youth and Adult Literacy Rate around the Globe dataset using the Get data option.

Step 2: Select both Adults_15YrsAndUp and Youth_15to24Yrs datasets available in the Youth and Adult Literacy Rate around the Globe file and then click on the Load button.

Step 3:

• Navigate to the Fields pane.
• Select the loaded table Adults_15YrsAndUp.
• Choose the New table option under the Table tools tab.

Step 4: Provide the name of the calculated table you want to create, enter the DAX expression in the formula bar, and click on the Commit icon to save the changes.

Here, we have created a Youth and Adult Literacy Rate table for this demo purpose. We are combining two different datasets with similar columns and also applying filters to extract only the data containing African sub-regions.

Youth and Adult Literacy Rate = UNION(CALCULATETABLE(SUMMARIZE(Youth_15to24Yrs, Youth_15to24Yrs[Region], Youth_15to24Yrs[Sub-region], Youth_15to24Yrs[Africa region], Youth_15to24Yrs[Africa sub-regions], “Total Male”, SUM(Youth_15to24Yrs[ Male ]), “Total Female”, SUM(Youth_15to24Yrs[ Female ])), FILTER(Youth_15to24Yrs, Youth_15to24Yrs[Africa sub-regions] IN {“Northern Africa”, “Southern Africa”, “Western Africa”, “Southern Africa”, “Central Africa”})),

CALCULATETABLE(SUMMARIZE(Adults_15YrsAndUp, Adults_15YrsAndUp[Region], Adults_15YrsAndUp[Sub-region], Adults_15YrsAndUp[Africa region], Adults_15YrsAndUp[Africa sub-regions], “Total Male”, SUM(Adults_15YrsAndUp[ Male ]), “Total Female”, SUM(Adults_15YrsAndUp[ Female ])), FILTER(Adults_15YrsAndUp, Adults_15YrsAndUp[Africa sub-regions] IN {“Northern Africa”, “Southern Africa”, “Western Africa”, “Southern Africa”, “Central Africa”})))

Step 5: Navigate to the Table view on the left side menu of the report canvas in Power BI to view the underlying data of the newly created calculated table.

Example #2 – Using CALCULATETABLE Function with Summarize Function

In this example, we will create a Power BI calculated table summarize using the CALCULATETABLE function with summarize function. For this demo, we will use the Big Mart Sales dataset. The Big Mart Sales dataset contains sales data for 1559 products across ten stores in different cities.

To create a Power BI calculated table summarize, follow the steps below:

Step 1: Open Power BI Desktop and import the Big Mart Sales dataset using the Get data option.

Step 2: Select the train_v9rqX0R dataset available in the Big Mart Sales file and then click on the Load button.

Step 3: Navigate to the Fields pane, select the loaded table train_v9rqX0R, and choose the New table option under the Table tools tab.

Step 4:

• Enter the DAX expression in the formula bar.
• Click on the Commit icon to save the changes.

Here, we have created a calculated table, Big Mart Summarized Table, for this demo purpose.

Big Mart Summarized Table = CALCULATETABLE(SUMMARIZE(train_v9rqX0R,train_v9rqX0R[Item_Type],train_v9rqX0R[Outlet_Type],train_v9rqX0R[Outlet_Establishment_Year],train_v9rqX0R[Outlet_Location_Type],”Outlet_Sales”, SUM(train_v9rqX0R[Item_Outlet_Sales])))

Step 5: Navigate to the Table view on the left side menu of the report canvas in Power BI to view the underlying data of the newly created calculated table.

Example #3 – Using CALCULATETABLE Function within Measures

In this example, we will use the CALCULATETABLE function within measures using the coursera_courses dataset. The coursera_courses dataset contains comprehensive data about All Coursera courses 2023.

The steps to use CALCULATETABLE are outlined below:

Step 1: Open Power BI Desktop and import the coursera_courses dataset using the Get data option and then click on the Load button.

Step 2: Select the coursera_courses dataset and then click on the Load button.

Step 3: Navigate to the Fields pane, select the loaded table coursera_courses, and choose the New measure option under the Table tools tab.

Step 4: Provide the name of the measure, enter the DAX expression in the formula bar, and click on the Commit icon to save the changes.

Here we have created a measure of Students enrolled in a course for this demo purpose.

Students enrolled in a course = SUMX(CALCULATETABLE(coursera_courses, coursera_courses[course_certificate_type] = “Course”),coursera_courses[course_students_enrolled])

Step 5: Navigate to the Visualizations pane, choose Table Visual, and then drag and drop the data fields from the Fields pane to the Columns section.

Step 6: Navigate to the report canvas to view the visualization.

You can further apply formatting by navigating to the Format your visual tab under the Visualizations pane and choosing the formatting options as appropriate for your report.

Important Things to Note

• Calculated tables are read-only and don’t support direct data input.
• Calculated tables do not persist in the data source and are recalculated when a data refresh occurs.
• Calculated tables are best suited where you want to perform calculations as an intermediate step and store data as part of the model
• The CALCULATETABLE function is not supported in DirectQuery mode if you are using any calculated columns or row-level security.
• When creating calculated tables, consider the performance impact, as complex calculations can slow down your Power BI model.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI Calculated Table. Here we learn how to create calculated field in power bi, with examples & points to remember. You can learn more from the following articles –

• Power BI Calculate
• Measures in Power BI
• Power BI Data Modeling