Joining two matrix visualisation based on the same rows

[WanqiYao]

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!

[v-achippa]

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

[WanqiYao]

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

[WanqiYao]

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       Employment

Oceania     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,
Wanqi

[Ashish_Mathur]

Am still confused.  I do not know what is the input/what is the result expected.

[lbendlin]

Please 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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[WanqiYao]

Hi! 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.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[krishnakanth240]

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.

[Ashish_Mathur]

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.

[lbendlin]

Create a geographic dimension table with your regions. Then use measures in your matrix visual for the individual value columns.