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

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;

SalesAmount using TreatAs = 
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    TREATAS(VALUES(DimCustomer[CustomerKey]),FactInternetSales[CustomerKey])
)

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;

TREATAS(VALUES(DimCustomer[CustomerKey]),FactInternetSales[CustomerKey])

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:

TREATAS(SELECTCOLUMNS(DimCustomer,'CustomerKey',DimCustomer[CustomerKey]),FactInternetSales[CustomerKey])

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

but you cannot just say as below:

TREATAS(DimCustomer[CustomerKey],FactInternetSales[CustomerKey])

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:

Customers with High School Eduction = 
CALCULATE(
    COUNTROWS(DimCustomer),
    TREATAS({'High School'},DimCustomer[EnglishEducation])
)

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;

qty by order date = 
CALCULATE(
    SUM(FactInternetSales[OrderQuantity]),
    TREATAS(
        VALUES(DimDate[DateKey]),
        FactInternetSales[OrderDateKey]
    ))

and one for Ship Date;

qty by ship date = 
CALCULATE(
    SUM(FactInternetSales[OrderQuantity]),
    TREATAS(
        VALUES(DimDate[DateKey]),
        FactInternetSales[ShipDateKey]
    ))

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:

Video

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.