Virtual Relationship in Dax | Demo with TREATAS Function

treatas-function-virtual-relationship-powerbi

Table of Contents

Virtual Relationship in Dax | Demo with TREATAS Function

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
Virtual Relationship in Dax | Demo with TREATAS Function

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. 
Virtual Relationship in Dax | Demo with TREATAS Function
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.
Categories
INDEXES
Database
Kaushlendra Mishra

What are Indexes in SQL Database? What is the advantage and disadvantage of Indexes?

Indexes are schema objects in a SQL database that can boost query performance while executing queries with a filter condition using where clause.

Indexes are used to retrieve data from databases very fast. An Index is a quick lookup table that is used to find a record that user search very frequently. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements.

Read More »

Leave a Comment

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