Downloads
DAXTable constructionIntermediate

DATATABLE

Creates a small table with the values typed into the formula.

Syntax
DATATABLE(<name>, <type>, [<name>, <type>] ..., { { <value>, ... }, ... })

Returns: A table.

What it does

DATATABLE creates a table from values typed into the formula. It suits a short list of assumptions, scenario rates, or a mapping that changes rarely. Because the values live inside the formula, the table is static. A change means editing the formula, not the source data. A typical use is a list of scenarios (base, upside, downside) that a slicer uses for a what-if measure.

Syntax and arguments

Example

A scenario table with a growth rate for each scenario, used in a sales forecast.

Scenario Rates =
DATATABLE (
    "Scenario", STRING,
    "Growth Rate", DOUBLE,
    {
        { "Base", 0.05 },
        { "Upside", 0.10 },
        { "Downside", -0.02 }
    }
)

The table has one row for each scenario. A slicer on the Scenario column then drives a what-if measure, such as the forecast sales times one plus the selected growth rate.

Example: a second use

A mapping of accounts to report lines, typed in so that anyone reviewing the model can see the assumption.

Account Map =
DATATABLE (
    "Account", STRING,
    "Report Line", STRING,
    {
        { "Product sales", "Revenue" },
        { "Returns", "Revenue" },
        { "Cost of goods", "Cost of sales" }
    }
)

Connect this map to other data with TREATAS or LOOKUPVALUE, using the account name as the key.

Things to watch

In business terms

Scenario rates for a forecast, a list of approval thresholds, or a small mapping of accounts to report lines. Putting the assumption in the model where reviewers can see it is often better than hiding it in a spreadsheet.

Related functions

All DAX functions · View as Markdown · DAX fundamentals