ExamFinch.com My Study

Study guide

4.4 Calculated Columns and Calculated Tables

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

4.4 Calculated Columns and Calculated Tables

Key Takeaways Explain This Topic AI

Calculated columns compute row-by-row in DAX and are stored in the model, increasing its size; they recompute on refresh.

Calculated tables create whole tables from DAX, commonly date tables (CALENDAR/CALENDARAUTO) and summary tables.

Prefer Power Query computed columns over DAX calculated columns when the source data is available, because they fold to the source and add no DAX overhead.

Calculated columns evaluate in row context and can feed slicers, filters, sort, and relationships; measures cannot.

Use a measure instead of a calculated column whenever the result must respond to filter context or aggregate.

Last updated: June 2026

Quick Answer: Calculated columns add columns to existing tables using DAX (evaluated row-by-row, stored in the model). Calculated tables create new tables using DAX (such as CALENDAR() for a date table). Prefer Power Query for column computations when possible; use a DAX calculated column only when you must reference the data model (relationships, other tables).

Calculated Columns

A calculated column adds a new column whose value DAX computes for each row. Create one with Table Tools > New Column . Full Name = [FirstName] & " " & [LastName]

  • Price Tier = IF([UnitPrice] > 100, "Premium", IF([UnitPrice] > 50, "Standard", "Budget"))
  • Category Name = RELATED(Products[Category])
  • Order Year = YEAR([OrderDate])
  • Profit = [Revenue] - [Cost]

Calculated columns evaluate row-by-row during refresh (row context), are stored in the model (consuming memory), recompute on each refresh, and can be used in slicers, filters, relationships, and visuals. They are not available on DirectQuery-only tables in the same way Import columns are.

When to Use a Calculated Column

  • Use Case Why
  • Reference a related table RELATED() needs row context
  • Build a key for a relationship Relationships need physical columns
  • Complex logic needing model data Nested IF/SWITCH over related tables
  • Column must traverse relationships Cannot be done in Power Query

When NOT to Use a Calculated Column (Use Power Query)

  • Use Case Why Power Query Is Better
  • Simple text manipulation Text.Upper , Text.Trim in M
  • Date extraction Date.Year , Date.Month in M
  • Concatenation & operator in M
  • Conditional logic on source data if-then-else in M
  • Static lookups Merge Queries

Power Query columns are processed during refresh rather than stored as DAX overhead, benefit from query folding (the transformation is pushed to the source database), keep the DAX engine lean, and separate ETL logic from analytics. As a rule, transform in Power Query, model in DAX.

  • Calculated Tables
  • A calculated table creates an entirely new table from a DAX expression ( Modeling > New Table ). Date =
  • VAR StartDate = DATE(2020, 1, 1)
  • VAR EndDate = DATE(2026, 12, 31)
  • RETURN
  • ADDCOLUMNS(
  • CALENDAR(StartDate, EndDate),
  • "Year", YEAR([Date]),
  • "Month Number", MONTH([Date]),
  • "Month Name", FORMAT([Date], "MMMM"),
  • "Day of Week", FORMAT([Date], "dddd"),
  • "Year-Month", FORMAT([Date], "YYYY-MM")
  • )

CALENDARAUTO() auto-spans the full range of dates across all date columns in the model, so Date = CALENDARAUTO() builds a contiguous calendar without explicit bounds. Other patterns: DISTINCT(Products[Category]) for a distinct-values table, and SUMMARIZE(...) for a summary table.

Calculated Table Characteristics

  • Property Behavior
  • Storage Fully materialized in the model
  • Refresh Re-evaluated on every data refresh
  • Relationships Can participate in relationships
  • Measures Can host measures
  • Limitation Not available in DirectQuery mode
  • Decision Tree for a New Column or Table

Computed from source data alone? Use Power Query .

Needs related tables via relationships? Use a calculated column (DAX).

Needs to aggregate or respond to filters? Use a measure instead.

Need a whole new standalone table? Use a calculated table (DAX).

The most common exam mistake is creating a calculated column where a measure belongs. If the value must change as users slice and filter (a total, a ratio, a running sum), it is a measure, not a column. A calculated column produces one fixed value per row at refresh time and never reacts to filter context.

On the Exam

The PL-300 tests choosing between calculated columns and measures, knowing when Power Query beats DAX for column computations, building a date table with CALENDAR()/CALENDARAUTO(), recognizing that calculated columns run in row context, and that they increase model size.

The Mental Model: Three Places to Compute

Every new value in a Power BI model can be created in one of three layers, and choosing the right layer is a core exam skill. Power Query (M) transforms data during load and is the right home for cleansing, type changes, derived attributes from source columns, merges, and any work that can fold to the source. Calculated columns (DAX) add stored, row-context values that depend on model relationships, the place for RELATED() lookups and keys you cannot build at the source. Measures (DAX) compute dynamically in filter context and are the right home for anything that aggregates or must react to slicers.

A clean model pushes work as far upstream as possible: transform in Power Query, model with calculated columns only when relationships are required, and express analytics as measures.

Why Calculated Columns Cost More Than They Look

A DAX calculated column is materialized and stored, so it consumes memory and is included in the model's compression. Worse, it is computed after Power Query load and after relationships are built, so it cannot fold to the source and runs entirely in the analytics engine on refresh. A column that could have been a folded Power Query step therefore both bloats the model and lengthens refresh. The exam rewards recognizing this: when a derived column depends only on columns in the same table or its source, Power Query is the efficient answer; reserve DAX calculated columns for genuine cross-table, relationship-dependent logic.

Calculated Tables in Practice

Beyond date tables, calculated tables are useful for what-if parameters (a disconnected table of values a user selects with a slicer), role-playing date copies , and summary/bridge tables built with SUMMARIZE , ADDCOLUMNS , DISTINCT , UNION , or CROSSJOIN . Because they re-evaluate on every refresh, keep their logic lean. A frequent design choice on the exam: build a small disconnected parameter table as a calculated table, then reference its selected value in a measure with SELECTEDVALUE to drive what-if analysis.