Combining Two Data Matrices into One

[annaharris]

Hi Everyone,

I am currently working on a project where I need to create a single matrix that displays data categorised by various metrics. I have successfully created two separate matrices but am struggling to combine them into one comprehensive matrix.

Here is my current setup:

• The first matrix pulls data based on a specific centre, selected from a slicer positioned in the top left of the matrix.
• The second matrix needs to compare the data from the chosen centre to all other data categorised as either 'Metro' or 'Non-Metro' based on location.

Can these two matrices be merged into one while maintaining the functionality of the centre-based filter? If so, how can I accomplish this without the filter interfering with the visualisation?

Any help would be very appreciated! 🙂

[v-xinruzhu-msft]

Hi,

Thanks for the solutions danextian ,  shafiz_p  and Ritaf1983  offered, and i want to offer some more information for user to refer to.

hello @annaharris , based on your picture, you can create a table to put the Centres as a dimensional table, then create 1:n relationship between the diemensional table and the fact table with the key [Centres].

Then put the Centres of diemensional table to the row, then put the related column of fact table  or measures to the values column , then it can work.

If the information cannot help you, can you provide some sample data so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

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

[danextian]

hi @annaharris 

Assuming they are from the same fact table, then you can just create a measure that filters to selected centre and the other one for Metro. If they are from different fact tables but share common dimensions, you can use a bridge table that is related to the fact tables that wil serve as a bridge between them. Without seeing a sample data that represents the actual one, it's hard to provide a more concrete solution.

[shafiz_p]

Hi @annaharris  It is hard provide exact solution with such few details. It is better to provide representative data and your desired output.

However, You could try the below codes to achieve this:

This mesure will only respond with the slicer selection:

SelectedCentreMetric = 
CALCULATE(
    [Measure],
    FILTER(
        'Table',
        'Table'[Centre] = SELECTEDVALUE('Table'[Centre])
    )
)

This measure will work for all location type either Metro or Non-Metro.

MetroNonMetroMetric = 
CALCULATE(
    [Measure],
    FILTER(
        'Table',
        'Table'[LocationType] IN {"Metro", "Non-Metro"}
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[annaharris]

I had a go with the code you provided but I can't use calculated fields as a Column in the matrix visual. Not sure if I'll need to create a new table as a source and link it to that?

I'm quite new to PowerBI so I'm still wrapping my head around the moving parts.

[Ritaf1983]

Hi @annaharris 

It is not possible to merge the tables visually, but you can create a single matrix with the necessary logic for comparisons by manipulating the filter using DAX. To provide a more concrete solution, it would help to understand better what you are trying to achieve.

You could create a small dummy file that illustrates the logic and shows the desired result, save it to a public drive, and share the link.

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