Showing results for 
Search instead for 
Did you mean: 

Virtual Relationship with TREATAS

Handling relationships is an essential aspect of any data modeling exercise. If done correctly, it can save a lot of time and effort. Power BI offers many-to-many relationships, but at times it adds many complexities. And hence, experts advise caution while using many-to-many relationships in Power BI.


TREATAS offers a relatively simple solution in this case. Let us understand this with the following example.


Following are two sample tables:

Table 1:




Table 2:






Calculate the Estimate vs. Actual report for each Project & Team.


As we notice, both the Project Number and Team have multiple values in both tables. If we establish a relationship, it is many-to-many.

For this exercise, we do not establish any relationship between the two tables.

Creating Basic Calculations


Added two measures for getting the total of Estimate and Actual Hours







Estimate Hours = SUM(table1[Estimate]) 
Actual Hours = SUM(table2[Hours])








Creating Measures using TREATAS


TREATAS applies the result of a table expression as filters to columns from an unrelated table. It returns a table containing all the rows in column(s) that are in the expression.








Total Hours Team =
   [Actual Hours],









In the above example, TREATAS is doing the following:

  • Using the VALUES function, I am supplying a list of all unique Teams and Project Number from table1
  • Using TREATAS to create a virtual many-to-one relationship on Teams and Project Number




I have used the measure in the table visual, which is producing the expected output.




Connect on LinkedIn

Follow on Twitter