TREATAS Function (DAX)

How does the TREATAS function (DAX) work?

The TREATAS function (DAX) applies the result of a table expression as filters to columns from an unrelated table.

TREATAS Formula Syntax

TREATAS (
     table_expression, <column> [, <column> [, <column> [, … ]]]}
)

How do you use the TREATAS function?

You can use this function to apply filters to a table while there is no relationship present. In a normal scenario, tables are related to each other and when one table is filtered, the filter will propagate using the relationship. With TREATAS, we can filter unrelated tables, which makes it a really useful function to have in your toolbox.

Related Blog Posts

Related Support Forum Posts

TREATAS – reconciling the numbers
Budgeting using TREATAS
Creating virtual relationships: TREATAS

Considerations when using the TREATAS function

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 are “Red” and “Green”.

Best for use when a relationship does not exist between the tables.

Related Video Tutorials

Formula examples using the TREATAS function

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

= calculate( sum(‘Fact Orders'[nb Orders]), TREATAS(VALUES(‘Order Date'[YearMonthnumber]),’Ship Date'[YearMonthnumber]) )

= DIVIDE( calculate( sum(‘Fact Orders'[nb Orders]), TREATAS(VALUES(‘Order Date'[YearMonthnumber]),’Ship Date'[YearMonthnumber]) ),calculate(sum(‘Fact Orders'[nb Orders])) )

Related DAX Functions

INTERSECT
FILTER Function (DAX)

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership