Forum Discussion
Use two relationships at the same time?
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?
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.
4 Replies
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 = UsersThe 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.
Thank you! Would you duplicate in DAX or in Power Query? Does it make a difference in terms of space or speed?
I would probaby do it in DAX but I don't use PQ neary as much.
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.
