PowerBI DAX UseRelationship
How to connect two columns in one table to another table within Power BI
As you create a data model with multiple tables with complexity, USERELATIONSHIP() will come in handy in so many ways. This blog is about one specific case when you have a table with multiple columns of the same values that mean different (such as, two date columns, two channel columns and so on). It will be easier to understand via specific scenario, so let’s start with that.
Example Scenario:
Let’s say you have a table that maps out orders by first touch channel vs. last touch channel, and also another table showing sessions by channel. Fake dataset is provided here.
Example screenshots below:
 |  |
Now, you want to show all the data by channel: the number of sessions, the first touch orders for that channel, and the last touch orders for that channel.
First, to connect the session table to the order table, we need to have a bridge table of channels (A, B, C, D) and then create a relationship. But when you do so, you will realize that you have to choose between first touch channel or last touch channel to connect with that bridge table. In this blog, we will resolve this issue so that you can start connecting one table to the bridge table as many times as needed.
TLDR;
Create both active and inactive relationships between the table and the bridge table.
Create measures with USERELATIONSHIP() so you can use the inactive relationship.
Step by Step Guide
Follow along with the same dataset (excel file here).
1. Create a bridge table
To start off, import the example dataset (.xlsx) into Power BI. Then let’s create a bridge table. To do so, I go to Table tools > New table, and use VALUES() to get distinct values of a column.
Here, you can do VALUES() on a different column such as Table1[First Touch Channel]. But note that you should not VALUES(Table1[Last Touch Channel]). That’s because the Last Touch Channel column is missing channel D. So if you use that column to create the bridge table, then you will not be mapping Channel D. Make sure you use the column with the most values.
Extra note on creating a bridge table:
If you are using VALUES() on a column with BLANK value, then you cannot create a relationship. In that case, you need to filter out BLANK value by doing: FILTER(VALUES(column1), NOT ISBLANK(column1))
Sometimes no one column is inclusive of all the possible values. In that case, you need to UNION the columns and then get distinct values. After doing VALUES() of each column, UNION it and then DISTINCT it. Example: DISTINCT(UNION(VALUES(A), VALUES(B)))
2. Create Active and Inactive relationships
connect the bridge table and session table
Connect either first touch or last touch channel to the bridge table. Here, I recommend connecting whichever channel you will use the most. For me, that would be the last touch channel so I will connect the last touch channel to the bridge table.
Lastly, connect the other channel in Table1 and bridge Channel. Automatically, it will show a dotted line instead of a solid line, which means it is an inactive relationship.
3. Create Measures
1. Basic SUM() measures
Let’s begin by creating all the metrics SUM() measures:
session = SUM(Table2[Sessions])
order = SUM(Table1[Orders])
Think about what the [order] metric we created above means. It sums up the order columns. So if you use the First touch channel column from Table1 as the dimension, it will divide the order by first touch channel, and if you use last touch, then it will divide the orders by last touch channel.
But if you use the channel bridge table to divide the orders, then it will use the last touch channel because that is the active relationship within Power BI. So now the question is, how do we use this [order] to also divide up by the first touch channel using the bridge table? For that, we create another measure (next step).
2. Create a Measure by Inactive relationship
For inactive relationships (first touch orders in this example), we have to use USERELATIONSHIP() to activate the relationship via a measure. Write it using CALCULATE():
First touch orders = CALCULATE([order], USERELATIONSHIP(‘Bridge Channel'[Channel], Table1[First Touch Channel])) This measure is saying, calculate [order] but use the relationship between bridge channel’s channel column and table1’s first touch channel column.
QA and end result
Now you are done! Let’s create the table we meant to create and QA:
First, let’s see how each channel has sessions and orders so we have the correct number to compare to. To do so, I created three separate tables that shows the correct number by simply summing sessions and orders:
Now, create a table that shows sessions and orders by channel (both first touch and last touch orders separately). To do so, you click a table and then add bridge channel, and metrics: [session], [first touch orders], [orders].
Comparing the result, we see that everything is correct. Something to note here, in the final table, last touch channel orders are showing up as [order] and it’s because, again, the last touch channel to bridge channel is an active relationship, so I could just simply put [orders] and it shows the order per last touch channel. I recommend changing the name of the column in the end result to make it more clear.
Conclusion
We went through a specific case, but this can be applied to other scenarios. The most common column type that can be in a table multiple times is a date time. If you have a table with multiple dates (purchase date, refund date, etc.) then you can use this same logic and apply to that case and show you the purchase and refund on a specific date.
Komentáře