Convert multiple column values to rows

[Saxon10]

I have 4 columns are Type, Id, Area & result and those 4 columns return multiple values. I would like to group the Type, Id and Area into one table.

Currently I am using multiple matrix table to achieve my desired result but it's painful so I would like to see all of them in one matrix visual table instead of multiple matrix visual tables.

I found similar query here but it was power query solution but I am looking DAX solution.

https://community.powerbi.com/t5/Desktop/Multiple-Rows-into-Multiple-Columns/m-p/543527

Any suggestion and advise please

Desired Result

I would like to achieve my desired result in one matrix table

[Mohammad_Refaei]

Hi @Saxon10 , can you share an example of the desired output? You can just draft the values in Excel sheet.

[Saxon10]

Thanks for your reply.

In Excel,  Based on my raw data I used two pivot tables and fillter by PR and TTR. 

[Mohammad_Refaei]

Hi @Saxon10 ,

Actually it's intriguing 🙂

I managed to create something like this:

Will this help?

[Saxon10]

Thanks for your reply again.

Yes.This is I want. Can you please advise how do you get the final result? its manual or did you make any formula or measure?

Could you please share the PBI file and I will try to replicate the same thing at my end. 

[Mohammad_Refaei · Accepted solution]

Well... I created a new calculated column:

 Area# = "Area" & RIGHT ( Data[Area], 1 )

and placed it in the column field in the matrix visual.

Then created a new measure and added it to the matrix values:

MyValues = 
IF (
    ISINSCOPE ( Data[ID] ),
    VALUES ( Data[Result] ),
    VALUES ( Data[Area] )
)

In the Matrix Row I added both the Type and ID.

In Power BI file options, I disabled the use of modern visual headers. 

And in the matrix visual format, I adjusted the subtotals as follows:

and made the column headers white (same as background to make  them invisible).

Please check this sample file.

[Saxon10]

Hi. Thanks you again and this is  brilliant solution. You provide new solutions for more than one layer of column headers in matrix visual.

Can you please advise one more thing here, if I changed the settings based on the your advice it don't affect exiting visual reports? 

[Mohammad_Refaei]

It is independent from other visuals as we calculated a custom value specifically for this visual (MyValues measure)

#update: disabling modern visual headers will affect all your visuals.

[Saxon10]

Thank you so much for your help. Much appreciated. I will check and update the feedback to you. 

[Mohammad_Refaei]

Did it work? or needs some optimization?

[Mohammad_Refaei]

Most welcome... Good luck

[Mohammad_Refaei]

I am not aware of any possibility to have more than one layer of column headers in matrix or table visuals (OPS1/OPS2/OPS2 and EPS1/EPS2/EPS3).