Forum Discussion
Joining two matrix visualisation based on the same rows
Hi team!
I currently have 2 tables sharing the same rows and I want to see how I can join them together to make the visualisation easier to see.
Table 1:
Region Employement Type 1 Employement Type 2
Oceania 100 50
Asia 199 34
Table 2:
Region Technical Support
Oceania 99 150
Asia 100 34
And I want to join the two matrixes such that the final table looks like
Region Employement Type 1 Employement Type 2 Technical Support
Oceania 100 50 99 150
Asia 199 34 100 34
Table 1 and table 2 are two independent data sources joined by a many to many relationship based on region. Please let me know if you have any good ways of achieving this. Thanks in advance!
Hi,
PBI file attached.
Hope this helps.
10 Replies
Hi krishnakanth240 Ashish_Mathur lbendlin , thank you so much for the replies! And apologies I wasn't clearer before. The original tables are in the format of:
Region EmploymentOceania Eployment Type 1
Oceania Eployment Type 1
Oceania Eployment Type 2
The table mentioned above is already using a count measure for the visualisation, likewise with the second table, so it would be a many to many relationship unfortunately, and joining the table wouldn't solve the issue.
lbendlin Could you kindly elaborate on how to achieve it? I'm not sure what you mean.Again, thank you so much for the replies.
Regards,
WanqiPlease provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi! Please see the data attached:
Table 1:Region Role Oceania Technical Oceania Technical Oceania Technical Oceania Support Oceania Technical Asia Technical Asia Technical Table 2:
Region Empoyment Type Oceania Permanent Oceania Permanent Oceania Temporary Oceania Temporary Asia Permanent Asia Permanent Asia Temporary These two are just examples and extract of a bigger table with different size, (table 1 and table 2 have different number of rows and columns in the original format) and thats why I couldn't combine the columns, from this I would like to create a visualisation where the count of each attribute of the same region in one; with the two tables above the visualisation should look like:
Region Permanent Temporary Technical Support Oceania 2 2 4 1 Asia 2 1 2 0
Once again, thank you so much for the help! and please let me know if this is not clear.
Am still confused. I do not know what is the input/what is the result expected.
Create a geographic dimension table with your regions. Then use measures in your matrix visual for the individual value columns.
Hi,
In Power Query, merge Table2 into Table1 based on the Region column. Then right click on the Region column and select "Unpivot Other Columns". Click on Close and Apply. Now create a matrix visual and write this measure
S = sum(Data[Value])
Hope this helps.
Hi WanqiYao
Create a dimension table with a Region field. Provide relationship from the dimension table to Table 1 and Table 2 based on Region field with 1 to M cardinality. Then write the individual measures for Employment Type 1, Employment Type 2, Technical, and Support with SUM aggregation.
Hi WanqiYao,
Thank you for reaching out to Microsoft Fabric Community.
Thank you krishnakanth240, lbendlin and Ashish_Mathur for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi!
Yep, it was exactly what I was looking for, thank you @krishnakanth240, @lbendlin and @Ashish_Mathur so much for the prompt advice! I was getting my head stuck on combining tables when I could have just worked around it. Thanks you so much!
Regards,Wanqi
