Value is the in of 1 label and out of another label, how to create the relationship for them?

[kidpk111]

Hi all,
My problem is I have a "Transaction" table like this:

anđ the dimension table like this

What can I do to create a matrix like this:

This is just a simplification of my data, it have a lot of rows so I don't want to split the "Transaction Table" into 2 and Union it

Is there any way to get this matrix most effectively ?
Thank you in advance.

[v-yangliu-msft · Accepted solution]

Hi  @kidpk111 ,

Here are the steps you can follow：

1. In Power Query – select [Revenue Type] and [Cost Type] Transform – Unpivot Columns.

Result:

2. Join the relationship between two tables.

3. Result:

Best Regards,

Liu Yang

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

[kidpk111]

Thank you for your answer, it work but it will duplicate may data and since my data is a lot it kinda reduce my performance

[amitchandak]

@kidpk111 , Duplicate the column Type of dimension table in power query and the use text before delimiters

Text Before Delimiter and Text After Delimiter: https://youtu.be/oGY4RoPKDOE

You can this new column on the column of matrix visual

or create measures like

Revenue =

calculate(Sum(Fact[Values]), filter(Dim, containsstring(Dim[Type] , "Revenue") ) )

cost =

calculate(Sum(Fact[Values]), filter(Dim, containsstring(Dim[Type] , "cost ") ) )

[kidpk111]

Thank you for your answer, but it still cant answer my question.
How can you create the relationship for this to work when you have a new column which contain only "Revenue" and "Cost" it need the correct relationship in order to put it into the matrix table. I can only create 1 relationship which is the Type from dim table to either Revenue Type or Cost Type I tried to use the function Userelationship() but It don't have the right number.
Can you help me with this ?