Use two relationships at the same time?

[bkr]

Hi gurus,

I have a simple dimension table with 3 guys in there:

Index	Name
1	John
2	Jack
3	Joe

and I have a fact table with transactions between these guys, where any of these 3 can be either sender or receiver:

Sender	Receiver	Value
1	2	7
1	3	8
2	3	9

Now I'd like to create a table or matrix visual that converts the indexes to names for both sender and receiver. It should look like this:

Sender	Receiver	Value
John	Jack	7
John	Joe	8
Jack	Joe	9

The only way I found so far is to create two identical dimension tables with exactly the same content. Would this be the way to go if the dimension table is huge, to double all the data?

[Anonymous · Accepted solution]

Hi @bkr ,

Based on your description, you can create a calculated table as follows.

Table 3 = SUMMARIZE(ADDCOLUMNS('Table 2',"_Sender",LOOKUPVALUE('Table 1'[Name],'Table 1'[Index],[Receiver]),"_Receiver",LOOKUPVALUE('Table 1'[Name],'Table 1'[Index],[Sender])),[_Sender],[_Receiver],'Table 2'[Value])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdbuchanan71]

I would probaby do it in DAX but I don't use PQ neary as much.

[jdbuchanan71]

Yes, that is the way to do it.  You can replicate a table using DAX simply by selecting New Table and pointing at the original.

Receivers = Users

The only other way I could think to do it would be with a sender / receiver pair created using a CROSSJOIN that would give you all possible combinations but that would end up bigger and less useful than just having a sender table and receiver table.

[bkr]

Thank you! Would you duplicate in DAX or in Power Query? Does it make a difference in terms of space or speed?