Matrix Visual - Dynamic Columns, Rows and Values

[newpbiuser01]

Hello,

I'm trying to create a matrix plot where the matrix visual is a dynamic table with the user being able to change the row/column headers along with the value being shown. As an example, if my table is as follows:

Category	Type	Location	Year	Month	Quarter	Cost	Quantity
Fruit	Apple	US	FY2021	FM1	FQ1	$0.5	1
Vegetable	Tomato	Canada	FY2020	FM12	FQ4	$1	5
Vegetable	Celery	Canada	FY2022	FM6	FQ2	$2	2
Fruit	Banana	US	FY2021	FM2	FQ1	$4	6
Fruit	Orange	US	FY2022	FM1	FQ1	$6	4
Fruit	Potato	Canada	FY2020	FM8	FQ3	$10	10

I'd like to create a matrix visual where the user has the option to select the column and row header(s) along with the metric they'd like to see. 

So, they would be able to select Cateogries and Type for row headers and Year-Month for the column headers along with the cost metric and the matrix table would look as follows:

In another case, the user could just select the Type for row header and FY-FQ as the column headers:

Or they could select just the Year option for the Column Header and the Type option for Row Header and select the Quantity metric:

I am able to switch between the values in the matrix, but am not able to view the nested column/row header values. Would anyone know if that's possible? I have tried to create unrelated tables for row and column headers and using slicers to do it, but toggling between Category and Type using a slicer does not show the breakdown Category and Type values - so show Fruit/Vegetable when I select Category and Apple/Banana etc. when I select Type. 

[perezco]

you only enable the personalized visuals to one tab , and with one visualization.

[newpbiuser01]

Hi @amitchandak unfortunately the ones here dont work because they require us using direct query to store our data table and given the size of the data table. 

For the personalization, would you know if we can limit which visuals we can turn on the personalization for? We don't want the users to be able to change all the visuals in our report or change the type of visual - just need to be able to make the axis dynamic. I'm not sure if its possible. 

[amitchandak]

@newpbiuser01 , For that you have unpivot all those columns, You can think of personalization

https://docs.microsoft.com/en-us/power-bi/consumer/end-user-personalize-visuals

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-personalize-visuals?tabs=powerbi-d...

Few approaches

Axis Slicer - Row/Column Slicer
Three approches from Guyinacube https://www.youtube.com/watch?v=dYmzb5UMkXw
Dynamically change chart axis in Power BI
bookmark -https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/
https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/