Downloads
DAXRelationships and lookupAdvanced

TREATAS

Uses the values of a table as filters on columns in another table, with no relationship in the model.

Syntax
TREATAS(<expression>, <column> [, <column> ...])

Returns: A table. Used as a filter inside CALCULATE, or as a table.

What it does

TREATAS takes the values of one table and applies them as filters on a column in another table, as though the two were related. Use it when two tables are not connected in the model: a budget kept by store name, a mapping table, or a selection that must filter a different table. It does not change the model. The filter follows the column you name as the target.

Syntax and arguments

Example

A budget kept in its own table, keyed by store name. The budget should follow the stores on the page. This assumes a Budget table with a StoreName column, which is not part of the Contoso sample.

Budget for Visible Stores =
CALCULATE (
    SUM ( Budget[Amount] ),
    TREATAS ( VALUES ( Stores[StoreName] ), Budget[StoreName] )
)

The store names visible on the page become filters on Budget[StoreName]. Each row shows the budget for its store, with no relationship needed.

Example: a second use

Sales for only the product categories that have a budget line. The budget uses category names, and the product list uses the same names.

Sales for Budgeted Categories =
CALCULATE (
    [Total Sales],
    TREATAS ( VALUES ( Budget[Category] ), Product[ProductCategory] )
)

The budget's categories filter the Product table, and the filter reaches Sales through the normal relationship from Product. This assumes the same Budget table as above.

Things to watch

In business terms

Budget and forecast tables kept at a different grain, mapping tables for account groupings, and any join where the keys are not modeled. Finance teams use it to compare budget with actuals when the budget file came from a different system.

Related functions

All DAX functions · View as Markdown · DAX fundamentals