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.


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



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.


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.



  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.

      • Is calculated tables with suitable use of cross join and filters to populate and then map out the relationships still a “preferred” method? This will in turn create som sort of a “snowflake” schema due to the differences in granularity between the actual fact and budget tables.

        So could TREATAS be seen as a way to keep the model simple at the expense of a lesser performance than creating (virtual) bridge tables with relationships when dealing with differences in granularity?

        • @Claus, I’m not sure that calculated tables are best practice, although it will work. Personally I prefer to use Power Query to create any lookup tables etc.

          TREATAS is definitely not as efficient as physical relationships, but yes I agree it can be a simplification on building bridge tables. There is no right or wrong way, just options

  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?

      • Try using TREATAS regardless of what IntelliSense suggests. Even though IntelliSense does not help you to type the function arguments, you can still use it. Yes, it will have that wavy red underlining, but it still works 🙂

  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.

  6. For anyone in managed healthcare in the US, this is a good way to compare daily claims activity and monthly capitation activity–especially if you are an end user with no control over the relationships created in your SSAS server-based tabular models.

  7. In the “Data Model” section above, in the 2nd bullet, the last two words should be “not allowed”.

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!