Mastering Virtual Relationships with TREATAS

Power BI has revolutionized data visualization and analysis for Business Intelligence. At the core of this powerful tool, lies the concept of relationships between tables which form the foundation for calculations and visualizations. While traditional relationships are essential but they sometimes fall short in handling complex visualization requirements.

Here the concept of virtual relationship emerges & "TreatAs" function is a game changer. TreatAs empowers users to establish dynamic connections between tables without creating physical links. This flexibility opens up new possibilities for data modelling and analysis. In this we will uncover how to effectively utilize this powerful DAX function to overcome data modelling challenges and unlock hidden insights within our Power BI reports.

Lets head over to Power BI Canvas to see how it works with a practical application:

For the presentation, I have imported financials table from sample dataset. So that readers can import it with ease and practice along with , from the option "Use Sample Data" as below:

Now lets create one table named as "Unique_Products" as below :

Unique_Products = values(financials[Product])        

Here we have used values function to get unique values of products from our fact table (Financials). These are the unique products present in the table :

Now we are having 2 tables in the data model :

1. Financials
2. Unique_Products

There is no physical relationship is defined between both the tables :

It means that both tables are independent and don't interact with each other.

Now our requirement is "User should be able to see the sales for a product that is selected from Unique_Products table. We should not establish a physical relationship between both the tables to achieve this".

In such scenarios concept of virtual relationships comes into picture. Because without this we can't create any link between both the tables. Here "TreatAs" empowers us to create dynamic virtual relationships between tables.

Lets See How it works:

Creating a first measure "Total_Sales" as below:

Total_Sales = CALCULATE(SUM(financials[ Sales]))        

Bringing this measure on a card visual and adding a slicer of product coming from Unique_Products table :

Here irrespective of selections made in the slicer, our value of Total_Sales on card visual remains same. As we know there is no relationship is present between both the tables and simultaneously no filters propagate from one table to other table.

In order to make it dynamic and to achieve our requirement , lets create a measure using "TreatAs" function:

Total_Sales_TreatAs = CALCULATE([Total_Sales],
TREATAS(VALUES(Unique_Products[Product]),financials[Product]))        

DAX Explanation: Here we are passing TreatAs function in the modified filter context of calculate formula. we are calculating Total_Sales against the modified filter context.

TreatAs function is table function (Output: Table) , here it is passed in the filter context. It basically takes a table expression as first argument (VALUES(Unique_Products[Product]) and in the second argument we pass a column (financials[Product])). Here a virtual relationship is built and filter flow propagates from argument1 to argument2.

In our case , Now Products coming from an un-related table (Unique_products) will be able to filter fact table (Financials) and user will get the sum for the selected product. Lets see this in below illustration:

This is completely dynamic and we have not touched the data model to achieve this requirement !

Bonus Tip :

TreatAs is the most compatible for use when a relationship does not exist between the tables. If we have multiple relationships (Inactive relationships) between the tables then one can go with UseRelationship function. As UserRelationship function converts an inactive relationship into an active relationship at the run time of calculation.

So in this article , we have explored a new level of data analysis and modelling capabilities using virtual relationship. Please feel free to share your inputs/suggestions/views on using virtual relationship. I hope this was helpful!