Virtual Relationship in Dax | Demo with TREATAS Function

Here, I will be discussing a very important dax function TREATAS which can be used to establish a virtual relationship between tables. I will provide steps that will help you to master this function.

Virtual Relationships are a very important concept in Dax. Whenever it is difficult to establish a relationship between two tables. A function such as TREATAS comes to the rescue and helps to establish the relationship.

Sample Data Model

Dataset used – Sample Super Store Dataset

Here, In the Data Model, We do not have any physical relationship between People and Orders but we want to find Sales by Person. So, Sales we have to get from Orders Table and Person is in the People Table.

Without any Relationship between Table

So, Without any relationship between the table if we try to place Sales and Person in a Table Visual. Let`s see what happens.

For each person, we are getting the total sum of Sales. It means Sales is not filtered by Person since there is no physical relationship between Orders and People Table.

With Virtual Relationship using TREAT AS Function

Now, Let`s try to establish a virtual relationship.
Step 1 –  Create a measure Salesbyperson and use Dax statement.
Salesbyperson =
CALCULATE (
    SUM ( Orders[Sales] ),
    TREATAS ( SUMMARIZE ( People, People[Region] ), Orders[Region] )
)
Step 2 – Use the Salesbyperson measure and see the magic. 

Now, we can see that Sales data from Orders Table is getting filtered by Person coming from People Table. 
Here, In the mentioned data model, we could have established a physical relationship between Orders and People table but there are some scenarios where it is difficult to establish relationships with a physical relationship and there is a need to establish a virtual relationship. At that point in time TREATAS function comes into the picture.
If you are interested to get the PBIX file for this demo, you can comment below with your email id. I will send you the PBIX file.
Thank You.

 Read More: .Ultimate Guide of Microsoft PowerBI Tutorials: Step by Step Approach