Calculated Tables in Power BI

What is a calculated table? A calculated table is a table you create inside the Power BI model with a DAX expression (instead of importing it from source). It’s evaluated at data refresh and stored in the model like any other table.

Remember: calculated tables are evaluated at refresh and are not dynamic to slicers — they reflect model state at refresh time.

How to create a calculated table

Step 1: Open Power BI Desktop.

Step 2: In Report View, Table View, or Model View go to the ribbon → Modeling (or Table tools in Table View) → New table.

Step 3: A calculated table is created by first entering the table name, followed by the equals symbol (=), followed by a DAX formula that returns a table. The table name can't already exist in the data model. The new table appears in the Data list.

In the Data pane, please note that the table icon has a calculator icon as well (denoting a calculated table).

Let’s create a date table is used for time-intelligence. We can create it with CALENDARAUTO() or by using min/max from our fact table.

Use CALENDARAUTO(): Creating Calendar table using CALENDARAUTO - covers all dates in model.

DAX

Calendar =
ADDCOLUMNS(
    CALENDARAUTO(),    // creates a contiguous single-column table of dates
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "YearMonth", FORMAT([Date], "yyyy-MM"),
    "Quarter", "Q" & FORMAT([Date], "Q")
)

In the below image we can see that the table is created, and it has a calculator icon in front of it, indicating it’s a calculated table.

Or use explicit min/max (safer when we only want the Sales date range):

DAX

Calendar =

// Calendar from Sales[OrderDate] range
VAR MinDate = MINX(ALL('Sales'), 'Sales'[OrderDate])
VAR MaxDate = MAXX(ALL('Sales'), 'Sales'[OrderDate])

RETURN
ADDCOLUMNS(
    CALENDAR(MinDate, MaxDate),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "YearMonth", FORMAT([Date], "yyyy-MM"),
    "Quarter", "Q" & FORMAT([Date], "Q")
)

Let’s go the Table view, to see the content of the table that it has created by using DAX.

Previous Next