Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to use the TREATAS function in DAX


By:   |   Last Updated: 2018-05-22   |   Comments (3)   |   Related Tips: > Analysis Services Development

Problem

The DAX query language – used in Analysis Services Tabular, Power Pivot and Power BI – contains some powerful functions. One of these functions is the relatively new TREATAS. In this tip, we’ll introduce you to the syntax and concepts of the function and show some uses cases where this function can be useful.

Solution

The TREATAS function applies the result of a table expression as a filter to the columns of an unrelated table. In other words, you can use it to apply filters to a table while there is no relationship present. In a normal scenario, tables are related to each other and when one table is filtered, the filter will propagate using the relationship. With TREATAS, we can filter unrelated tables, which makes it a really useful function to have in your toolbox.

The syntax is as follows:

TREATAS(table expression, column[, other optional columns])

Let’s illustrate the concept of the function with a simple example. The following model is an import from the AdventureWorks data warehouse, where only a few columns have been kept:

simple data model

The Sales Territory dimension is directly related to the fact table, but employees also have a sales territory. In the model a relationship is created, but it is not active, as it would result in ambiguity. We can say the employee dimension and the sales territory dimension are unrelated, as no bi-directional filtering is enabled anywhere in the model. With TREATAS, we can create a measure that when the sales territory region in the employee dimension is filtered, it will also filter the sales territory dimension for the same region. The result will only be sales done in a sales territory by employees of that sales territory. Sales from employees outside the territory will be discarded. The formula is as follows:

SalesAmount (excl Blended Sales) = CALCULATE(SUM(FactResellerSales[SalesAmount]),TREATAS(VALUES(DimEmployee[SalesTerritoryRegion]),DimSalesTerritory[SalesTerritoryRegion]))
			

We can verify the result when comparing the measure with the original SalesAmount measure:

treatas example

In the screenshot above, we filter on the Northeast employee territory. The sales excluding “foreign” employees are visibly lower. Keep in mind the filter on the sales territory dimension is only applied in the measure itself. The table on the bottom – displaying all the regions from the dimension – is unaffected by the slicer.

Another example of using TREATAS is described in the tip DAX Enhancements in Analysis Services 2017.

Dynamically Switching Dimensions

With TREATAS, you can solve some scenarios in a creative way. Suppose we want to dynamically change the columns on a matrix by using a slicer. This scenario is inspired by the blog post Dynamically switching axis on visuals with Power BI by Kasper de Jonge.

Initially we have the following matrix:

start matrix

By selecting another value on a slicer, we want to change columns of the matrix automatically:

end matrix

The data comes from a Tabular model using the WideWorldImporters data warehouse as a source. The tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3 has a walkthrough on how you can create such a Tabular model.

The key to the solution is to create a disconnected table holding all the possible values that can be put on the columns axis of the matrix. It will serve two purposes:

  • The grouping of those values will be displayed in the slicer. E.g. “buying groups” and “years”.
  • The individual values will be displayed on the columns axis.

Let’s create a view in the WideWorldImporters data warehouse that will fetch the data in the above format:

CREATE VIEW dbo.DynamicDimension AS
SELECT DISTINCT
    [Category]  = 'Years'
   ,[Values]    = CONVERT(VARCHAR(30),[Calendar Year])
FROM [WideWorldImportersDW].[Dimension].[Date]
UNION ALL
SELECT DISTINCT
    [Category]  = 'Buying Groups'
   ,[Values]    = [Buying Group]
FROM [WideWorldImportersDW].[Dimension].[Customer];	

The result set contains the following data:

result set

Now we need to add this view as a new table in the Tabular model. Depending on how your model was built, you can either create a new query with the “modern get data” functionality or you can add a new table using the legacy connectors. The tip What's New in Analysis Services Tabular 2017 has a section on the modern get data experience and on how you can use the legacy connectors.

import new table

Choose the newly created view from the list and import it into the model. There is no need to create a relationship with any of the other tables.

import table

The next step is to create a measure that will change depending on the value we have chosen in the Category column. It will need to do the following:

  • Check if only one single category is chosen. If multiple values are chosen – e.g. Years and Buying Groups – nothing should be returned.
  • Inspect the value chosen for category and push it as a filter to the corresponding dimension using TREATAS.

The formula becomes:

DynamicQuantity:=
IF(HASONEVALUE(DynamicDimension[Category])
    ,SWITCH(VALUES(DynamicDimension[Category])
                        ,"Years",CALCULATE(SUM('Order'[Quantity]),TREATAS(VALUES(DynamicDimension[Values]),'Date'[Calendar Year]))
                        ,"Buying Groups",CALCULATE(SUM('Order'[Quantity]),TREATAS(VALUES(DynamicDimension[Values]),Customer[Buying Group]))
    )
)
			

As you can see, the result is blank when nothing is selected:

dynamic measure with TREATAS

The HASONEVALUE function checks for the first criteria, that only one category should be selected. The SWITCH function will evaluate the selected category and return the correct calculation corresponding with the chosen category. The calculation itself is the sum of quantity, where the category is pushed down as a filter to the correct dimension using TREATAS by using CALCULATE.

We can now create our dynamic matrix:

dynamic matrix

The Values column of the DynamicDimension table is put on the columns axis, while Sales Territory remains on the row axis. The DynamicQuantity measure is put in the values field. In the screenshot above you can see both categories are displayed in the columns when no selection is made in the slicer. However, the Total column is blank thanks to the use of HASONEVALUE. By selecting items in the slicer, we can change the layout of the matrix:

dynamic matrix ftw

If you want to make the rows dynamic as well, the formula becomes more complex as you need to take the various possible combinations of the rows and columns intersections into account. The blog post Virtual Filters Using TREATAS has examples of how to use TREATAS with multiple columns.

Other Use Cases

The main concept of TREATAS will always be the same: create a virtual relationship between tables. But you can use it in various situations:

Next Steps


Last Updated: 2018-05-22


get scripts

next tip button



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, October 05, 2018 - 11:09:38 AM - Diego Back To Top

Great.  Thanks for checking out my post.  
I'm gonna try some more on that because it looks very cool. 
I've seen that post before, and maybe I just need to digest it a little bit. 

Have a good one. 


Friday, October 05, 2018 - 9:59:45 AM - Koen Verbeeck Back To Top

Hi Diego,

I asked Kasper de Jonge (who works at Microsoft and has played a role in SSAS and Power BI, so he knows a lot about DAX) and he says it's not possible. However, there are other methods and he says to check out this post: https://www.kasperonbi.com/dynamic-relationships-based-on-the-selection/


Thursday, October 04, 2018 - 10:42:54 AM - Diego Back To Top

Hi Koen, thanks for posting this.  I think this is a cool feature that I want to exploit more. 

I'd like to present a problem that I'm working on, and ask for your help hoping that you might engage as I have.  In fact, working on that is how I reached your post before, and it's related to the TREATAS function.  

I've posted in the PowerBI Comunity forum in search for a solution.  The link is this one .

For a summary, I want to change the periodicity by which I see my reports from week to month.  Hopefully I can add functionality on top of my main Calendar table, and don't need to redefine the metrics in my model. 

Thus I did create a few tables that change the periodicity from week to month.  
And linked them with virtual filters to this main table. The problem is that I can't use this virtually filtered table with the rest of the model.  
The rest of the details are in the post that I mentioned.  

Thank you for looking at this.  
Regards from Mexico. 


Learn more about SQL Server tools