Creating a new table from an existing table in Power BI is a core data modeling technique that enables you to build summary tables, lookup tables, filtered subsets, and calculated reference tables without duplicating source data imports. DAX (Data Analysis Expressions) provides several powerful functions for this purpose, including SELECTCOLUMNS, SUMMARIZE, ADDCOLUMNS, FILTER, and DATATABLE. This guide covers each method with practical examples for enterprise reporting scenarios.

Understanding Calculated Tables in Power BI

Calculated tables are tables created using DAX expressions that derive their data from other tables in your model. They are computed during data refresh and stored in memory alongside your imported tables.

When to use calculated tables — Creating lookup/dimension tables, building role-playing dimension tables, generating date tables, creating filtered subsets for specific report pages, and building summary tables for performance optimization
When to avoid calculated tables — When the transformation can be done in Power Query (M language) during data load, which is generally more efficient for large datasets
Memory impact — Calculated tables consume memory in the data model; use them judiciously in large models with millions of rows
Refresh behavior — Calculated tables recalculate during every data refresh, so their content stays in sync with source data

Method 1: SELECTCOLUMNS — Create a Table with Specific Columns

SELECTCOLUMNS creates a new table by selecting and optionally renaming columns from an existing table. This is the most common method for creating a subset table.

ProductLookup =
SELECTCOLUMNS(
    Products,
    "Product ID", Products[ProductID],
    "Product Name", Products[ProductName],
    "Category", Products[Category]
)

Creates a new table with only the specified columns
Column names can be renamed in the output by providing new name strings
Useful for creating dimension/lookup tables from denormalized fact tables
Combine with DISTINCT or VALUES to remove duplicate rows

Method 2: SUMMARIZE — Create Aggregated Summary Tables

SUMMARIZE groups data by specified columns and can include aggregated measures, making it ideal for creating summary and rollup tables.

SalesSummary =
ADDCOLUMNS(
    SUMMARIZE(Sales, Sales[Region], Sales[Category]),
    "Total Revenue", CALCULATE(SUM(Sales[Amount])),
    "Order Count", CALCULATE(COUNTROWS(Sales))
)

SUMMARIZE creates distinct groupings; ADDCOLUMNS adds calculated aggregates
This pattern (SUMMARIZE + ADDCOLUMNS) is the recommended approach over using SUMMARIZE with aggregation expressions directly
Ideal for creating executive summary tables that pre-aggregate large fact tables
Use ROLLUP or ROLLUPGROUP within SUMMARIZE for subtotals and grand totals

Method 3: FILTER — Create Filtered Subset Tables

FILTER creates a new table containing only rows that meet specified conditions, useful for creating focused tables for specific business units, time periods, or status categories.

HighValueOrders =
FILTER(
    Orders,
    Orders[Amount] > 10000
    && Orders[Status] = "Completed"
)

Creates a table with all columns from the source but only matching rows
Multiple conditions can be combined with && (AND) and || (OR)
Useful for creating role-based views or department-specific data subsets
Combine with SELECTCOLUMNS to both filter rows and select specific columns

Method 4: DISTINCT and VALUES — Create Unique Value Tables

DISTINCT and VALUES extract unique values from a column to create lookup or reference tables.

UniqueCategories = DISTINCT(Products[Category])

RegionLookup =
DISTINCT(
    SELECTCOLUMNS(
        Sales,
        "Region", Sales[Region],
        "Country", Sales[Country]
    )
)

DISTINCT returns unique rows from a table expression (excludes blanks for single columns)
VALUES returns unique values including the blank row that represents missing relationships
Combine with SELECTCOLUMNS to create multi-column dimension tables from denormalized sources
Essential for star schema modeling where dimension tables need to be extracted from flat tables

Method 5: Power Query (Recommended for Large Datasets)

For large-scale data modeling, creating derived tables in Power Query (M language) during data load is generally more efficient than DAX calculated tables, as transformations happen during refresh rather than consuming in-memory resources.

Reference queries — Right-click a query in Power Query and select "Reference" to create a new query based on the existing one
Duplicate queries — Create an independent copy of a query that can be modified without affecting the original
Group By — Use the Group By transformation to create summary tables during data load
Remove Columns — Use Remove Other Columns to create lean lookup tables from wide fact tables
Merge Queries — Join multiple queries to create combined reference tables

Why Choose EPC Group for Power BI Data Modeling

EPC Group has over 28 years of enterprise business intelligence experience, with deep specialization in Power BI data modeling and DAX optimization. As a Microsoft Gold Partner, our Power BI architects have designed data models for Fortune 500 companies handling billions of rows across healthcare, finance, retail, and manufacturing. Our founder, Errin O'Connor, authored the bestselling Microsoft Press book on Power BI and leads a team of certified data modeling specialists.

Enterprise Power BI data model architecture and star schema design
DAX optimization for complex calculated tables and measures
Power Query ETL development for large-scale data transformations
Composite model design with DirectQuery and Import mode
Performance tuning for models with billions of rows

Optimize Your Power BI Data Models

EPC Group's Power BI architects can help you design efficient data models, write optimized DAX, and build enterprise-grade reporting solutions. Contact us for a Power BI data modeling assessment.

Schedule a Consultation Call (888) 381-9725

Frequently Asked Questions

What is the difference between a calculated table and a Power Query table?

A calculated table is created using DAX and computed in memory after data load. A Power Query table is created using M language during the data load/refresh process. Power Query tables are generally more memory-efficient because they fold transformations back to the data source when possible. Calculated tables are more convenient for DAX-based logic that references measures and the model context.

Do calculated tables affect report performance?

Calculated tables consume memory in the data model and increase refresh time since they must be recalculated during every refresh. For small to medium tables (under a few million rows), the impact is minimal. For very large derived tables, consider using Power Query instead, which can leverage query folding for better performance.

Can I create relationships with calculated tables?

Yes. Calculated tables behave like any other table in the data model. You can create relationships between calculated tables and imported tables using standard relationship configurations. This is a common pattern when extracting dimension tables from denormalized fact tables to build a proper star schema.

How do I create a date table from an existing table?

Use the CALENDAR or CALENDARAUTO function to generate a date table. CALENDARAUTO automatically scans all date columns in your model and creates a complete date range. You can then enrich the date table with ADDCOLUMNS to add Year, Quarter, Month, Week, and DayOfWeek columns. Mark the table as a Date Table in the model for proper time intelligence.

Can I use UNION or INTERSECT to combine tables?

Yes. DAX provides UNION (combines rows from two or more tables), INTERSECT (returns rows common to two tables), and EXCEPT (returns rows in the first table that are not in the second). These set operations are useful for creating consolidated tables from multiple source tables, such as merging regional sales tables or combining actual vs. budget data.