Forum Discussion
Add/Remove Columns of Matrix Visual Using Slicer
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.
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.
Hi MFelix ,
Thanks again for your reply. Visually, this is exactly what I wanted, however when exporting the data, the Allocated/Attended/Planned Capacity columns collapse into a "Measure" column whereas ideally they would remain as separate columns in the exported CSV file just like how they do for the top matrix in the file you shared (I do not mind this happening for the Contract YR and Month columns).
Is it possible for those 3 columns to remain separate in the exported CSV?
Thank you so much for your help.
Hi oliverblane ,
Since this a single measure when you export the values for CSV it will appear in a single column and the measures names will be presented on a different column.
Do you need to export to CSV? There is a new option to export the table/matrix in the exact view you have on the service.
I am glad you pointed that out - I did not realise there was that option in the service. Thank you very much for your help!
MFelixI'm trying to do something similar and essentially wondering if there is a way for the result in a switch line to contain multiple items. Ex. note how the Attended line in the switch function below contains AND (I know this doesn't work, but it's essentially what I'm going for).
Selected Measure value = SWITCH( SELECTEDVALUE(Matrix_Selection[Measure]), "Allocated" , SUM(Measure_Selection[Allocated]), "Attended", AND(SUM(Measure_Selection[Attended]), SUM(Measure_Selection[OtherRange]), "Planned Capacity", SUM(Measure_Selection[Planned Capacity]) )In my case, if Allocated is selected I want to show that, but if Attended is selected I want to show multiple columns.
