DAX Function Guide

TREATAS
Empty image or helper icon

Sam McKay

CEO & Founder

How does the TREATAS 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?

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

Loading

Considerations when using the TREATAS?

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”.

Related Video Tutorials

Loading

Formula examples using the TREATAS

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

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

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

Related Courses

Loading