How to Create Virtual Relationships using TREATAS Function in Power BI DAX

How to Pass or Propagate filters from a unrelated table column values as input using TREATAS Function in Power BI DAX
The TREATAS Function applies the result of a table expression as filters to columns from an unrelated table. It treats the columns of the input table as columns from other tables. For each column, filters out any values that are not present in its respective output column.

It treats the columns of the input table as columns from other tables. For each column, filters out any values that are not present in its respective output column. It creates a virtual relationship is a DAX pattern to transfers a filter context from a table to another, simulating the behavior of a physical relationship defined in the data model. This technique is useful whenever a relationship does not exist, or when it cannot be created because the relationship is not a one-to-many, or because it is defined by two or more columns.

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

Notes :

table_expression - An expression that results in a table.

column - One or more existing columns. It cannot be an 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 are "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.

Scenario :

Suppose we have the Data Model as follows, where we have two un related tables (tbl_Region ; tbl_Cost)

Example 1 :

From the above Model, we can calculate(measure) the Sales per Region using the TREATAS Function as follows.

Sales per Region = CALCULATE([Total Sales],
TREATAS(VALUES(tbl_Region[Region_Id]),tbl_Sales[SalesRegion_Id]))

Note :

The TREATAS Function will create a Virtual relationship between the tables tbl_Sales and tbl_Region. Next it passes the values of the Region_id from unrelated table to the Sales table.

here, [Total Sales]=SUM(tbl_Sales[Net_Sales])

Result :

Example 2 :

Now we will calculate(measure) the Total Cost from the unrelated table tbl_Cost , by matching the values of Year and MonthNum of both the tables (tbl_Calendar, tbl_Cost).

Total Cost = CALCULATE( SUM(tbl_Cost[COGS]),

             TREATAS( SUMMARIZE(tbl_Calendar,tbl_Calendar[Year],tbl_Calendar[MonthNum]),

              tbl_Cost[Year],tbl_Cost[Month])

                    )

Result :

Example 3 :

Now we will calculate(measure) the Sales for a specific Year, Quarter and Region. In this example, we are just passing values to 3 columns as in list using TREATAS Function.

2014Q1EuropeSales = CALCULATE( SUM(tbl_Sales[Gross_Sales]),

TREATAS( {(2014,"Q1-2014","EUROPE")}, tbl_Calendar[Year],tbl_Calendar[FiscalQuarter],tbl_Region[Region_Name]))

Result :

Example 4 :

In the following example, we will created a Table, using TREATAS and Summary functions, where Month, Year values exist in both the tables (tbl_Calendar, tbl_Cost).

TreatAsTable = TREATAS( SUMMARIZE(tbl_Calendar,tbl_Calendar[Year],tbl_Calendar[MonthNum]),tbl_Cost[Year], tbl_Cost[Month])

Regards, Tamatam