Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Hi @Saxon10 , can you share an example of the desired output? You can just draft the values in Excel sheet.
Thanks for your reply.
In Excel, Based on my raw data I used two pivot tables and fillter by PR and TTR.
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.
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.
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?
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.
Thank you so much for your help. Much appreciated. I will check and update the feedback to you.
Did it work? or needs some optimization?
Most welcome... Good luck
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |