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 now allowed.
  • The same applies to the Calendar[Year]
  • The same applies to the Products[Category].

image

Without Relationships the Visuals Wont 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.

image

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

image

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

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

Share?

Comments

  1. Ah, interesting solution to the granularity problem! Rob and Avi have a different solution in their book, but I’ll bet this has significantly better performance at the cost of being slightly less flexible than actually creating a bridging table.

  2. Another good article Matt and I’ll spend some time tinkering with this to make it stick

    What are the pros and cons of using Values vs SelectedValue to identify the fact a filter is active in a visual?

    • Values and selectedvlaues do very different things, but both respect the filter context. Values returns a table that has lineage to the Data model. Selectedvalue returns a scalar value. If you write if(hasonevalue(tablename[column]),values(tablename[column])) you can also extract the scalar value using values, but it is still a scalar value,I think 🙂

  3. Got it, thanks

    For some reason I thought you were taking the actual selected value active in the visual but on reading properly it makes better sense

    The word ‘lineage’ really makes this register and reminds you that the first argument needs to be VALUES, i.e. a table

  4. Hi Matt, very interesting post. You mentioned it’s not available in Power Pivot 2010 or 2013. I wanted to try it using Power Pivot 2016 (Monthly channel), but the function TREATAS doesn’t seem to be available. Is there an activation step I’m missing?

  5. Hi Matt. It’s interesting that you wondered about the function name TREATAS. The same did I when I first saw it. TREATAS looks like a word that actually exists and you should know about. It took me a few attempts to arrive at TREAT-AS. TREAT the values of the first parameter AS filter values on the column of the second parameter. I would personally have preferred TRANSFERFILTERS or something like that.

    Small comment. When you separate two columns from the same table like in:

    TREATAS(VALUES(Calendar[Year]), Budget[Year]),
    TREATAS(VALUES(Calendar[Monthname]), Budget[monthname])

    You run the risk of introducing an arbitrarily shaped filter into the calculation and some confusion for the users. This will be dependent on the existence of unnatural hierarchies and the tool you are using (Power Pivot, Power Bi etc.) That’s another reason for using SUMMARIZE. I ‘m not sure it’s possible to create this situation in Power BI but it’s definitely possible in Power Pivot.

    Best regards Jes.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x