The TREATAS function applies the values contained in the columns of a table as filters on columns of another table not necessarily related to the first one.
TREATAS(
table_expression,
column
[, column...]
)
- table_expression: Expression that returns a table or name of an existing table.
- column: Name of the column or columns of the second table to which the table_expression values will be applied as a filter.
The TREATAS function returns a table.
The number of columns indicated as arguments must match the number of columns in the table indicated by the table_expression argument and have the same order.
In the case that said number of columns is equal to one, the column is simply filtered according to the values present in table_expression. If the number of columns is greater than one, the values of the columns that exist in table_expression will be applied as filters to the columns one by one. That is, if in table_expression we have columns A, B and C and in the column arguments we have specified columns a, b and c, the values of column A will be applied as a filter to column a, the values of column B will be applied as a filter to column b, and the values in column C will be applied as a filter to column c.
Values contained in the table_expression table that do not exist in the remote table are ignored.
Suppose we start from the following data model:
...in which each sale is associated with a country, a region and a city. The countries included in this table are:
If we create the following measure:
Sales = SUM(Sales[Amount])
...we can take it to a card-like display to show the total sales:
...knowing that this figure will be modified by any selection or filter we make in the Geography table.
Now we introduce a new table of countries (Countries) in the model, a table not related to the previous ones:
The TREATAS function allows us to use values from this last table to filter the Geography table. For example, let's create a segmentation based on the Name field of this table:
Note that we have selected four countries of which only two are present in the Geography table.
Now we create the following measure:
Sales countries =
CALCULATE(
[Sales],
TREATAS(
Countries,
Geography[Country]
)
)
In it, we are calculating the Sales expression applying as filter values in the Geography[Country] column the selected values of Countries (in this case the table in question has a single column, so we only have one column argument in the expression).
The result is as follows:
Countries selected in the Countries table that are not present in the Geography[Country] field are simply ignored.
