Forum Discussion
Matrix and Unpivot Column
Hi,
I'm presenting a data via a matrix and I would like the user to be able to manually select the columns that will be displayed. I tried unpivoting the columns so I can select the columns using a slicer but I can no longer add another parameter (Region). The attached screenshot shows the the two visuals - first is the normal matrix and my desired output minus being able to manually select the columns to be displayed; second is where the columns can be selected but needs to have the "Location" added. Is it possible to do this?
Hi CA2021
According to your statement, I know the result you want is like first martix. However, you couldn't select columns in level2 like(Books, Chairs...) by slicer.
I think you can transform second matrix to achieve your goal. Add 'Region' in Matrix Column instead of Matrix Row and it should be one level above 'Attribute'. Then you can use drill up/down function.
Click 'Expand all down one level in the hierachy' in Red box. Then you can get your desire result and you can select columns by slicer.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
CA2021 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
In this case location, region and attributes should be columns
Thanks, amitchandak.
Attached is the sample .pbix file. The first page is my desired output visually speaking. The change that I would like to have with it is to be able to manually select the columns the matrix has.
Ont he other hand, the second page is what I was able to create. The Regions is in rows and not columns.
The goal is I would like to compare the values of my Attributes side by side and not in a column (visually seen in Page 1).
Here's the link to the .pbix file: https://drive.google.com/file/d/1xAK8ZEe_nSv0hmbPDwixJUH3g7l4JEDx/view?usp=sharing
Hi CA2021
According to your statement, I know the result you want is like first martix. However, you couldn't select columns in level2 like(Books, Chairs...) by slicer.
I think you can transform second matrix to achieve your goal. Add 'Region' in Matrix Column instead of Matrix Row and it should be one level above 'Attribute'. Then you can use drill up/down function.
Click 'Expand all down one level in the hierachy' in Red box. Then you can get your desire result and you can select columns by slicer.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This does it.
Thank you Anonymous
The limitation you've hit is a fundamental constraint of the unpivot approach once you unpivot columns into an Attribute/Value structure, you can only use one dimension as the slicer pivot axis. Adding Region as a second dimension creates the cross-join problem you're seeing.
The cleanest native workaround: use Field Parameters for column selection (no unpivoting needed) and put Region as a separate row in the Matrix hierarchy. This preserves both dimensions without restructuring the data.
For full flexibility users selecting both which columns appear AND having Region as an independent dimension this is exactly the gap Flexa Tables on AppSource fills. Users configure columns and row dimensions directly in the published report without any data model changes
