Building a Virtual Relationship in Power BI – Basics of TREATAS DAX Function

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

TreatAs is an interesting function with multiple use cases in Power BI. I thought it is good to explain it in a set of articles, the first one here is about what the TreatAs function is and how it can help to build filters and a virtual relationship between two tables. Let’s see how you can use this function.

Sample Data Model

The sample data model that I use here has three tables with no relationship (Best practice for the below model would be to create the relationship, however, I intentionally removed the relationship to explain the concept to you through TreatAs function);

Filtering Values Using TREATAS

If I have a visual using EnglishEduction (from DimCustomer table), and SalesAmount (from FactInternetSales table), then it would look like below;

The reason, of course, is not having the relationship between the two tables: DimCustomer and FactInternetSales. I have explained in another article how the relationship acts as a filter in Power BI.

Now, to understand the TreatAs function, let’s see how the structure of function usage is;

TreatAs(<expression>,<column 1>,<column 2>…)

The way that you should read TreatAs is this:

Treat <expression> As <column 1>,<column 2>….

Now, there are some rules, in getting TreatAs working

  • Expression; the expression part of the function above is a table expression, it means an expression that returns table. That means any function that returns a table can be used here; Summarize, Values, Distinct, ….
  • Order of Columns in the table expression should be the same as the order of columns in <column 1>,<column 2>…

Considering the two rules above, here is how you can use TreatAs to filter the FactInternetSales table using the value of EnglishEduction in DimCustomer;

The result would be filtered by EnglishEduction, even though there is no relationship between the two tables;

Now, let’s look at the TreatAs expression a bit more closely;

We connected the two tables using the CustomerKey. We are saying that TREAT DimCustomer[CustomerKey] AS FactInternetSales[CustomerKey]. It means to filter the FactInternetSales[CustomerKey] as of it is DimCustomer[CustomerKey]. Or in other words; If DimCustomer[CustomerKey] is filtered to show only CustomerKey XYZ, then FactInternetSales[CustomerKey] would be also filtered to show only CustomerKey XYZ. It is the same concept of having a relationship, but let’s say a virtual relationship.

The VALUES part of the statement is because the EXPRESSION part should be returning a table not a column. VALUES is returning the unique list of DimCustomer[CustomerKey] column. You can use other options such as below too:

The expression above will return the same result as Values in the visual mentioned above.

but you cannot just say as below:

This will give you an error that you cannot use a column name in the expression that expects a table expression.

Using Scalar Values and TREATAS

You will get the most value of TreatAs when you use a derived table using a DAX function such as VALUES or any other function that gets the result of a selection from a table. However, you can even use TREATAS with a scalar table definition. Previously in my article here I explained that you can use a table constructor to build a table in DAX expression.

So it means, I can even have calculations like this:

and the result will be count of all customers with their EnglishEducation as High School;

The {“High School”} is a single value table (single row and single column) that filters the DimCustomer[EnglishEduction].

Virtual Relationship Using TREATAS

The first example that you saw, used TREATAS to filter the SalesAmount in FactInternetSales by the value selected from DimCustomer. You can use this approach to create a virtual relationship. Let’s say you want to do a role-playing-dimension and filter the OrderQuantity using OrderDate and ShipDate both. I have explained how you can use the UseRelationship method or cloning the date table as a role-playing dimension before. here is another way of doing it using TreatAs.

I have a measure for Order Date;

and one for Ship Date;

And as a result, with no relationship between the DimDate and FactInternetSales I get the calculations working perfectly fine;

Note that I am not saying that it is recommended to implement relationships this way (for both the above examples, it is better to create an actual relationship between tables rather than using TreatAS). All I am showing here to you is the basics of using TreatAs, and how you can virtually create the relationship using this function. In the next article, I’ll show examples of how you can use it to filter based on multiple fields.

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 yearsโ€™ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Rezaโ€™s passion is to help you find the best data solution, he is Data enthusiast.

4 thoughts on “Building a Virtual Relationship in Power BI – Basics of TREATAS DAX Function

  • Hi Reza,

    thanks for dedicating time to this new intriguing function. Two points:

    1) the “Treat This as That” explanation – are you sure? Even from the paragraph that you’ve written “..It means to filter the FactInternetSales[CustomerKey] as of it is DimCustomer[CustomerKey].”,it seems that the order is in revers (treat as that this), meaning the Fact table column is “this” and dimension “that” (the column determining how the other one is treated). Whoa its confusing.

    2) Looking forward to more real-world examples where this actually comes in handy

    • Hi Radek
      That might be my English problem, and you might be right in the order.
      The bottom line is that the COLUMN(s) will be filtering values based on the result of the EXPRESSION. Would that be called the Treat the Expression As Column? or Treat the Column as Expression? good practice for my English learning for sure ๐Ÿ˜Š
      Cheers
      Reza

Leave a Reply

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