Virtual Filters Using TREATAS - Excelerator BI

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

image

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.

Power BI DAX Book

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.

Power BI Online Training

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.

31 thoughts on “Virtual Filters Using TREATAS”

  1. Thank you for this post , It’s very helpful function.
    I am willing to know if there’s a way to define a filter on the join , a dynamic one because most of the time I come across static filter such as TREATAS({“Red”, “Green”, “Yellow”}, DimProduct[Color]).

    what I want to achieve is to specify a treat as between 2 tables A et B where date.TableA > date.TableB

    is it doable ? thx

    1. I can’t see why not. I would suggest a disconnected table as the input (eg, 1, 2, 3, 4, 5). You can select a value, but it doesn’t filter another table. Then you can use FILTER to push that filter onto the other table.
      CALCULATE([something],FILTER(dataTable,dataTable[number]>=max(DisconnectedTable[Number])))

  2. Thanks for your very very helpful explanation!

    In my data model, there is a dimension table as assignments and the sales table with many to many relationship. Despite of a bridge table in our model for connecting these two tables , I want to keep some filters in a relationship and do not keep some other columns. I would appreciate if you advise me in this regard. Using CROSSFILTER(……..,None) for removing filters and then Treatas simultaneously is a right solution or not?

    Thanks for your support in advance!

    1. I can’t see why your suggestion would not work – you will have to give it a try. If you want to selectively keep some filters and not others, I guess you could use ALL(table[column]) too.

  3. Thank you this great post. Though, I have a question: I ran a regular SUMMARIZECOLUMN vs your approach using TREATAS, in fact, I notice there is no enhanced performance of TREATAS. I might be wrong, please correct me.

    EVALUATE

    SUMMARIZECOLUMNS(

    Tenant[Customer Dashboard],
    Tenant[Tenant ID],
    Tenant[Tenant Name],

    “Entitlements”,[Entitlements]

    )

    EVALUATE

    SUMMARIZECOLUMNS(

    Tenant[Customer Dashboard],
    Tenant[Tenant ID],
    Tenant[Tenant Name],

    “Entitlements”,CALCULATE([Entitlements],TREATAS(SUMMARIZE(Tenant,Tenant[Customer
    Dashboard],Tenant[Tenant ID],Tenant[Tenant Name]),
    Tenant[Customer Dashboard],Tenant[Tenant ID],Tenant[Tenant Name])))

    1. I can’t be sure of the performance impacts, but I will take your observations on that. There are often many ways to do a task and it is common for the developers to have handled inefficiencies under the hood automatically for you.

  4. Thanks for this great post Matt… I too think of this as treat x as y. Thanks for sharing the SUMMARIZE tip, will definitely use that from now on.

    I just watched a ‘guy in a cube’ vid with Phil Seamark.. He showed an example of optimizing a matrix, and used a measure in his example where a number of different variables were used using TREATAS to establish explicit filters… i.e. TREATAS({Red},Product[ProdColor]). Still trying to wrap my head around optimization, and the best ways to define filters.

  5. It really is an awesome function.

    I don’t reckon TREAT this AS a filter for that quite nails it though…

    I think it’s more like treat the values in THIS as the selected values in THAT.

    For example treat VALUES ( PRODUCT[category] ) as BUDGET[product category]

    treat as

    It’s also interesting to note that the column names can be different

  6. Interesting discussion on how TREATAS affects lineage at https://www.sqlbi.com/articles/understanding-the-data-lineage-in-dax/

    “Even though the data lineage is kept and maintained by the engine in a completely automatic way, the developer has the option of changing the data lineage of a table. TREATAS is the function accomplishing this task. TREATAS accepts a table as its first argument, followed by a list of column references. TREATAS returns the same input tables, with each column tagged with the data lineage of the column references specified as arguments. “

  7. Matt, Thanks for this. I have used this but my solution has been a little more difficult in its application. I have a view where I need to display the GlCode by month, and I have sourced the data from a fact table which has numerous transaction. Without drill-down, I wanted to show the Sum of transactions for the GLCode for the month and compared to the Budget for the month. The drill-down opens up a view of the underlying transactions.
    It seemed to work but the Treatas formula is still not working to fix the problem.
    Budget:=CALCULATE (
    SUMX ( GenBudgetsCO, [Bud] * RELATED(GLCodeGroups[Sign] )),
    TREATAS (
    SUMMARIZE (
    GenTransCO,
    GenTransCO[GlYear],
    GenTransCO[GlPeriod],
    GenTransCO[Group],
    GenTransCO[Code],
    GenTransCO[Calendar Month]
    ),
    GenBudgetsCO[GLYear],
    GenBudgetsCO[GL Period],
    GenBudgetsCO[Group],
    GenBudgetsCO[Code],
    GenBudgetsCO[CalendarMonth]
    )
    )
    The problem is that there are occasions when there are no transactions (Actual) in the month, and therefore the Treatas (This month) from the fact table is not propagating to the Budget table and it returns a blank value when in fact there is a Budget value. Really struggling with this and I am about to move into the new financial year with a known issue. Suggestions welcome.

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

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

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

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

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

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

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

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

      1. Claus Thorning Madsen

        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?

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

Leave a Comment

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

Scroll to Top