In this blog post, I want to share how I create a virtual relationship by using DAX formula when no physical relationships were found.
When creating visualizations by using columns from two tables, but there is no relationship between the two, then I try to create a virtual relationship to make one table filter another.
It is quite obvious that physical relationships are the most wanted long term solution for making performance better and managing datamodel easier. However, virtual relationships provide interesting solutions when creating physical relationships is not possible.
I will try to demonstrate it by using a sample that analyzes performance segmentation and plan (aggregated table) vs. actual.
Performance segmentation
I use a sample that looks like below to present how I create virtual relationships.
I write some measures to visualize the information like below.
QTY: = SUM( Sales[Quantity] )
QTY P.M: = CALCULATE( [QTY:], PREVIOUSMONTH('Calendar'[Date]) )
Diff %: = DIVIDE( [QTY:] - [QTY P.M:], [QTY P.M:] )
I also wanted to group or segment the monthly growth percentage (Diff %) like below.
As seen from the above table's structure, there is no way to create a physical relationship with the existing tables in the datamodel. As a result, after loading it, the datamodel looks like below.
Monthly growth %, or monthly growth % by product can be grouped like below after creating a measure like below.
DISTINCT DAX function is used in this measure in order to get the value from the [Tier] column in the Group table. One thing to note here is that DISTINCT returns column table. However, if the result is one-row-one-column-table, it can be also shown as value, like in this case, and it can be presented as a result of a calculaed measure.
If, you want to see a grouping analysis like below, for instance, how many customers are in each group in August, a calculated measure can be written like below.
Plan (aggregated table) VS. Actual analysis
Addition to the above datamodel, when the comparison between Plan VS. Actual is needed, sometimes you will get the below type table from the management. You can notice that the Product table shown in the above sample can have one to many relationship with the product column below. However, Month-Year column in the below figure is not easy to be connected to the calendar table. Using a bridge table to create a physical relationship is one way to solve the issue, but in this writing, I will try to present a solution by creating a virtual relationship.
As shown in the below datamodel, Plan table is not connected to Calendar table. And, of course, it is not connected to Customer table, because there is no information about Customer.
I use TREATAS DAX function to create a virtual relationship.
It seems, monthly level number is correct. However, because there are no information about customers in Plan table, the numbers about customers are wrong. And, seeing the date level information, like below, the same number is all over the place.
There are many ways to solve this issue. One is to make it have the same portion as actual (or previous period's actual). Other way is to not show numbers if there is no information. In this case, I will show blank if the granularity is different. In order to show blank value on where I want, I need to fix the measure like below.
Instead of using ISFILTERED, you can also try to use other functions like ISINSCOPE, ISCROSSFILTERED, or HASONEVALUE functions. Selecting a proper DAX function depends on how datamodel looks like and how you want to show information in visualizations. But for now, I think ISFILTERED is good.
To summarize, DAX functions can help creating a virtual relationship when creating physical relationships is not feasible. In the situation of analyzing performance segmentation or plan vs. actual, considering to create virtual relationships is one of good choices to make.
I hope this helps to have more fun in creating connections between tables that seem like having no common columns.
Comments