Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kidpk111
Helper I
Helper I

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

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

kidpk111_0-1675687225568.png

anđ the dimension table like this

kidpk111_1-1675687340004.png

What can I do to create a matrix like this:

kidpk111_2-1675687408077.png

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.

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @kidpk111 ,

 

Here are the steps you can follow:

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

vyangliumsft_0-1675752707013.png

Result:

vyangliumsft_1-1675752707014.png

2. Join the relationship between two tables.

vyangliumsft_2-1675752707015.png

3. Result:

vyangliumsft_3-1675752707018.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @kidpk111 ,

 

Here are the steps you can follow:

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

vyangliumsft_0-1675752707013.png

Result:

vyangliumsft_1-1675752707014.png

2. Join the relationship between two tables.

vyangliumsft_2-1675752707015.png

3. Result:

vyangliumsft_3-1675752707018.png

 

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

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
Super User
Super User

@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 ") ) )

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 ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.