Forum Discussion
Add/Remove Columns of Matrix Visual Using Slicer
I want to be able to add and remove columns from a matrix visual using a slicer, very similar to how it is done in the solution of this question: https://community.powerbi.com/t5/Desktop/Adding-Removing-matrix-columns-based-on-Slicer-Filter/m-p/254107
However, my problem is that I have multiple column hierarchies for my problem. In the context of the question I attached, an example would be having Year and Month above the columns Europe/Specific/etc... (in the solution of that question). Their solution does not seem to work for this scenario. Is there a way to do this?
Edit
The aim is to be able to select which of the Allocated/Attended/Planned Capacity columns to show in the matrix using a slicer.
Thanks!
Hi oliverblane ,
For this you need to create a disconnected table with the following format:
Now add the following measure:
Selected Measure value = SWITCH( SELECTEDVALUE(Matrix_Selection[Measure]), "Allocated" , SUM(Measure_Selection[Allocated]), "Attended", SUM(Measure_Selection[Attended]), "Planned Capacity", SUM(Measure_Selection[Planned Capacity]) )Add the Measure column from the previous table on the column below the Month, and the measure above on the values.
Result below and in attach PBIX file.
Has you can see the matrix on the bottom only show selected measures.
9 Replies
Hi oliverblane ,
Without knowing the details is difficult to pin point the correct way, but taking into account that you are refering you have several columns hierarchies, I would create a disconnected table with those hierarchies and then create a switch measure to use on the values.
When you refer months and years on the hierarchie are you refering to 2020, 2021, 2022 and Jan, Feb, ..., Dec or is it on a different format.Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.Hi MFelix, thank you very much for your reply.
Here is a link to a simplified version of my report with anonymous data: https://meganexuslimited-my.sharepoint.com/:u:/g/personal/oliver_blane_meganexus_com/EUrLLCs7Up1Gn19OQ4glYP8B3C021f6qlESOKtrjISUobw?e=gBnrlm
Ideally I would like a slicer that can allow users to select which of the Allocated/Attended/Planned Capacity columns they would like to see in the matrix. For example they might only wish to look at Allocated, or Allocated with Attended, or all three at once perhaps.
Do you know if this is possible? I appreciate your help!
Hi oliverblane ,
For this you need to create a disconnected table with the following format:
Now add the following measure:
Selected Measure value = SWITCH( SELECTEDVALUE(Matrix_Selection[Measure]), "Allocated" , SUM(Measure_Selection[Allocated]), "Attended", SUM(Measure_Selection[Attended]), "Planned Capacity", SUM(Measure_Selection[Planned Capacity]) )Add the Measure column from the previous table on the column below the Month, and the measure above on the values.
Result below and in attach PBIX file.
Has you can see the matrix on the bottom only show selected measures.
Here is my Cross Apply SQL Query to unpivot the columns. That way you can have the your base data and the columns that you want to turn on/off in the unpivot.
SELECTtbl.lngId,tbl.[Student Id],tbl.[Student Name],tbl.Grade,tbl.Counselor,tbl.Enrolled-- ,tbl.Code-- ,tbl.Cal,tbl.Birthdate -- DataType: Date-- ,tbl.Gender,tbl.[Site]-- ,tbl.[Date]-- ,tbl.[Description]-- ,tbl.Comments/* These are the unpivoted columns:,tbl.[Period],tbl.[Semester],tbl.[Subject],tbl.strSection,tbl.Title,tbl.Teacher,tbl.Assignment,tbl.Portal,tbl.Points,tbl.[Spcl-Mark],tbl.[Eff Score],tbl.Possible,tbl.Grades,tbl.[Class Avg]*/-- ,tbl.CreatedDate -- DataType: DateTime2-- ,tbl.LastRefreshed -- DataType: DateTime2,UnpivotedData.Attribute,UnpivotedData.ValueFROM [dbo].[tblMaterializedStudentGradebookSummary] tbl/*Note: Cross Apply Values (Attribute, Value) for output.*/CROSS APPLY (VALUES('Period', CAST(tbl.[Period] AS VARCHAR)),('Semester', tbl.Semester),('Subject', tbl.[Subject]),('strSection', tbl.[strSection]),('Title',tbl.Title),('Teacher',tbl.Teacher),('Date', CAST(tbl.[Date] AS VARCHAR)),('Assignment', tbl.Assignment),('Portal',tbl.Portal),('Points', CAST(tbl.Points AS VARCHAR)),('Spcl-Mark',tbl.[Spcl-Mark]),('Eff Score', tbl.[Eff Score]),('Possible', tbl.Possible),('Comments', tbl.Comments),('Grades', tbl.Grades),('Class Avg', tbl.[Class Avg]),('Description', tbl.[Description]),('Created Date',CAST(tbl.CreatedDate AS VARCHAR)),('Last Refreshed',CAST(tbl.LastRefreshed AS VARCHAR)),('Enrolled',CAST(tbl.Enrolled AS VARCHAR)),('Code', tbl.Code),('Cal',tbl.Cal)-- ,('attribute', tbl.Birthdate) -- DataType: Date,('Gender', tbl.Gender),('Site',tbl.[Site])--,(attribute)-- ,(UnpivotedData.Value)) AS UnpivotedData( -- Transform Columns to Rows.Attribute, Value);
