Forum Discussion
Dynamic column selection for matrix visual
Hi Anonymous ,
To what I can understand you want to have an option for users to select columns that they see on the matrix table that is not an option you have on PBI by default however you can create a workaround.
Create an unrelated table with all your Columns Names that you want to have as on off on your matrix.
In my case I created a table named Selector:
Columns selection
Quantity Value Then add the following measures:
Column selection = MAX(Selector[Columns selection]) Totals Columns = SWITCH([Column selection];"Quantity";[Quantity total];"Value";[Value total])
On the second measure you should add all the related measure that you have for each column you want to show.
Now create your matrix in the following way:
Rows: Category (this are the columns that you want to have the details (keep the ones you have)
Columns: Selector[Columns Selection]
Values: [Totals Columns]
Should give the result below:
See attach PBIX.
Regards,
MFelix
Hi Anonymous ,
To what I can understand you want to have an option for users to select columns that they see on the matrix table that is not an option you have on PBI by default however you can create a workaround.
Create an unrelated table with all your Columns Names that you want to have as on off on your matrix.
In my case I created a table named Selector:
Columns selection
| Quantity |
| Value |
Then add the following measures:
Column selection = MAX(Selector[Columns selection]) Totals Columns = SWITCH([Column selection];"Quantity";[Quantity total];"Value";[Value total])
On the second measure you should add all the related measure that you have for each column you want to show.
Now create your matrix in the following way:
Rows: Category (this are the columns that you want to have the details (keep the ones you have)
Columns: Selector[Columns Selection]
Values: [Totals Columns]
Should give the result below:
See attach PBIX.
Regards,
MFelix
Hello Miguel,
We can create a matrix without having anything in the "columns" section. However for the dynamic column logic which you demonstrated here, when i remove "Selector[Columns Selection]" from the columns section, the matrix gives error. Can you please help me by explaning this behavior.
Regards,
Rajnish Singh
Regards,
Rajnish Singh
Hi Anonymous,
When you use the normal fields on a matrix you don't need to have values on the columns or rows, however that won't mae it dinamic since the value is on the matrix or not.
In this case since the need is to show/hide selected values there is a need of context and that is given by that column of the Selectors table.
If you notice the first part of the measure is SELECTEDVALUE that forces the context transition to show or hide the column so if you take it out the measure does not work.