TREATAS - Daxplained

TREATAS is a DAX function that treats the columns of an input table as columns from other tables, filtering out values not present in the output columns.

Syntax

TREATAS(
   Expression,
   [ ColumnName, ... ]
)

Argument	Properties	Description
Expression		The expression that generates the set of columns to be remapped.
ColumnName	Repeatable	The name of the output column.

Return Values

A table that contains all the rows in column(s) that are also in table_expression.

Remarks

The number of columns specified must match the number of columns in the table expression and be in the same order.
If a value returned in the table expression does not exist in the column, it is ignored. For example, TREATAS({“Red”, “Green”, “Yellow”}, DimProduct[Color]) sets a filter on column DimProduct[Color] with three values “Red”, “Green”, and “Yellow”. If “Yellow” does not exist in DimProduct[Color], the effective filter values would be “Red” and “Green”.
Best for use when a relationship does not exist between the tables. If you have multiple relationships between the tables involved, consider using USERELATIONSHIP instead.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example

In the following example, the model contains two unrelated product tables. If a user applies a filter to DimProduct1[ProductCategory] selecting Bikes, Seats, Tires, the same filter, Bikes, Seats, Tires is applied to DimProduct2[ProductCategory].

CALCULATE(
    SUM(Sales[Amount]),
    TREATAS(
        VALUES(DimProduct1[ProductCategory]),
        DimProduct2[ProductCategory]
    )
)

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/treatas-function-dax