Merger or join or combine two matrix

[umairarshad]

Hello pbi users,

I need to merge/combine/join tow matrix.

  

I want to put the "GSE Count" column before "S/A" column.

Please help me out in this regard.

Regards,

[bhanu_gautam]

Hi @umairarshad , you can do merge in Power Query editor

In the Power Query Editor, select one of the tables you want to merge.
Click on the "Home" tab, then select "Merge Queries".
Choose the second table you want to merge with the first one.
Select the columns you want to use for the join from both tables

And then create a matrix visual and you can reorder column accordingly

[umairarshad]

You mean I can merge in same query?

[bhanu_gautam]

Yes, if column are from same table or have relation 

[umairarshad]

Thak you for the prompt response.

These above both matrix are from same query.

[bhanu_gautam]

Go to first visual and pull GSE Count column in visual if it is from same table, and rest two column after it 

[umairarshad]

I tried it already but it output two "Count of GSE" columns each for S/A and U/S column.

I want single count column

[Anonymous · Accepted solution]

Hi @umairarshad ,

@bhanu_gautam Thanks for your concern about this case!

And @umairarshad , I'm guessing that the reason you have two columns of GSEs in your matrix is because you put Ageing in the Rows of the matrix, Status in the Columns of the matrix, and then you put GSEs in the Values, which by default returns a separate column of GSEs for each element in the Columns.

You can try this way:
Here is my sample data:

Use these DAXs to create measures:

S/A status = 
VAR _Count = 
CALCULATE(
    COUNT('Table'[Status]),
    'Table'[Ageing] = MAX('Table'[Ageing]) && 'Table'[Status] = "S/A"
)
VAR _Total = 
CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Ageing])
)
RETURN
_Count / _Total

U/S status = 
VAR _Count = 
CALCULATE(
    COUNT('Table'[Status]),
    'Table'[Ageing] = MAX('Table'[Ageing]) && 'Table'[Status] = "U/S"
)
VAR _Total = 
CALCULATE(
    COUNTROWS('Table'),
    ALLEXCEPT('Table', 'Table'[Ageing])
)
RETURN
_Count / _Total

Count of GSE = 
CALCULATE(
    COUNT('Table'[GSE]),
    ALLEXCEPT('Table', 'Table'[Ageing])
)

Place Ageing in the Rows of the matrix, don't put any fields in the Columns, and place the above three measures in the Values, and the final output is as below:

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