Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Added two measures for getting the total of Estimate and Actual Hours
Estimate Hours = SUM(table1[Estimate])
Actual Hours = SUM(table2[Hours])
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 =
CALCULATE(
[Actual Hours],
TREATAS(VALUES(table1[Team]),table2[Team]),
TREATAS(VALUES(table1[Project_Number]),table2[PR_Number])
)
In the above example, TREATAS is doing the following:
I have used the measure in the table visual, which is producing the expected output.
Cheers!
Vivek
https://www.vivran.in/
Connect on LinkedIn
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.