Virtual Filters Using TREATAS

Level: Intermediate

I have been aware of the TREATAS function for a very long time.  I have tried to understand how it works a few times but could never really work it out. I think part of the problem is the function name TREATAS is not very descriptive.  To contrast this point, the FILTER function filters a table, the ALL function returns all rows in a table, the SUM function sums up all the values in a column, but TREATAS?!  What does that do?  It was simply not that intuitive to me.

Edit: Thanks to Jess for posting an explanation to the function name in the comments section.  You can think of TREATAS like this.

TREAT this AS a filter for that.  More on this below.

What TREATAS Does

The TREATAS function can be used to detect filters from your visual (filter context) and then apply these filters to a disconnected table in your data model.

• It takes a source table (first parameter) and applies the values from that table to columns in a target table (second and subsequent parameters).
• You can use a function like VALUES as the first parameter to detect the initial filter context in a visual and hence TREATAS can propagate filter context to the target table.
• You do not need to have a physical relationship between the source table and the target table.  It therefore means that TREATAS can be used as a virtual many to many relationship.
• You can pass multiple filters (columns) from the source table to the target table.  TREATAS can therefore can be used to apply multiple relationships (ie on more than one column) between tables.

Data Model

For this article I am using my modified Adventure Works database as the starting point.  I then loaded a budget table to the data model which contains budget by month and category for 2004 & 2005.  It is not possible to join the new budget table (shown below) directly to the existing lookup tables.  Why?

• There are 2 columns from the Calendar table that need to join to the Budget table.  It is only possible to have 1 active relationship between tables in DAX.
• The budget has been created at the month level of granularity and the calendar table is at the day level.  If I try to join Budget[Month] to Calendar[Month] it will be a many to many relationship and that is not allowed.
• The same applies to the Calendar[Year]
• The same applies to the Products[Category].

Without Relationships the Visuals Won’t Work

There are 2 matrices below, both have Calendar[Month Name] on Rows, Product[Category] on Columns and a filter applied for 2004.  The matrix on the left shows the [Total Sales] measure and the data model is correctly filtering the actual sales data.  The matrix on the right is not correctly filtering because there are no physical relationships between the lookup tables and the Budget table.

Enter TREATAS

TREATAS takes a table as the first parameter and then applies the values in the column(s) from this table and propagates these onto the columns in the second and subsequent parameters.  The TREATAS function can therefore be used to pass the filter context in a visual from the source table(s), and them apply this filter context to the target table.   See the example below

The first parameter (1 above) is VALUES.  VALUES returns a single column table that respects the initial filter context from a visual.  This filter context is then passed to the second parameter (shown as 2 above). TREATAS then returns a filtered copy of the target table that can then be used inside a CALCULATE to apply the filters to a measure.  A real example will make it easier to understand.  TREAT 1 AS a filter for 2.

Using TREATAS to Propagate Filters

Using the Adventure Works data model from above, I want to detect the filters coming from Product[Category] in the visual and apply those filters to the Budget table without the need to have a physical relationship.  Here is the formula that will do this (shown below).

Note the first parameter inside TREATAS (line 4 above) is a VALUES over the lookup table – this is the initial filter context I am detecting from the visual.  The second parameter is the column that will receive this filter.  This has the affect of placing a filter from the Products[Category] table onto the Budget[Category] column in the Budget table even though there is no physical relationship between the tables.

Filtering on Multiple Columns

I can then repeat the pattern to operate over multiple columns from the same tables, or different tables as follows.

In each TREATAS function above, the VALUES function detects the filters on the selected lookup table column and then TREATAS applies this filter to the Budget table.

Performance Improvements

After writing the above measure, I decided to check the performance using DAX Studio. I cover how to use DAX Studio in detail in this article here.  I launched DAX Studio and tested the performance of the measure as shown below.

Improved TREATAS Formula using SUMMARIZE

I have learnt a lot from The Italians, especially when it comes to table functions and performance optimisation.  I read this article by Marco Russo about TREATAS and decided to test out the performance of my measure above against the alternative syntax Marco used using SUMMARIZE.  I cover the use of SUMMARIZE in my DAX Query series that starts here; it is easy to use when you know how it works.

SUMMARIZE returns a table of all the unique combinations of columns in the initial filter context.  The trick to using SUMMARIZE across multiple lookup tables is to always start from a common data table.  Take the data model shown below.

To create a SUMMARIZE table that contains filters from more than 1 lookup table, you must use the Sales table as the first parameter.

=SUMMARIZE(Sales,
        'Calendar'[Year],
        'Calendar'[MonthName],
         Products[Category]
    )

This virtual table created by SUMMARIZE can then be used inside the TREATAS function to pass the filters from all 3 columns to the budget table all at once.

This SUMMARIZE table is a virtual table that contains 3 columns and enough rows to cover all the unique combinations of Year, MonthName and Category coming from the lookup tables in the Initial filter context.  TREATAS then applies the filters from the 3 source columns onto the columns in the Budget table as specified in lines 6, 7 & 8 above.  When I tested this new measure in DAX Studio I got much better performance.

After retesting a few times, I found that the SUMMARIZE version was consistently 3 x faster than the first version with multiple TREATAS functions.  You can’t really rely on the timing when the queries run as quickly as this, but I tested the performance a few times and got a fairly consistent result.

A Final Word

The TREATAS function is relatively new and is not available in some of the older versions of Power Pivot.  It is not available in Excel 2010/2013/2016 but it is available in Power BI Desktop.  If you are using Excel, you can use INTERSECT instead of TREATAS.  INTERSECT effectively does the same thing.  You should note however that the syntax is back to front.  The first parameter is the table that receives the filter and the second parameter is the filter that is to be applied.