Forum Discussion
Dynamic column selection for matrix visual
hi
i have the columns along with calculated measures in the matrix visual . currently everything is static and matrix visual as a scroll and it is becoming hard for the users to come up with the analysis on columns list . On adhoc basis they want to remove and add the columns in matrix visual similar power pivot functionality . Is this possible to acheive along with calculated as measure of this
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
26 Replies
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 MFelix ,
Thanks! This works well for me. However, I'd like to add month columns on top of the columns selection (New Budget and Rolling Forecast columns). When I did that, the columns selection did not show up.
Before I added month columns
After I added month columns
Would it be possible to do multiple headers in this case? The month columns don't need to be dynamic. It would be a plus if it can be.
Thank you in advance!
Hi ttt,
When you add more than one column on a row or columns part of a matrix you get an hierarchy and you can drill down to lower levels, if you check the header of the table you have a arrow that splits into two if you click it you will go to the lowest level showing the months and the the measures.
Hello MFelix,
thanks for your solution. Is it also possible to do this when the columns are not measures but "normal" columns? So I have a table with many columns and i want my users to choose which columns to display in the matrix visual. I am not sure if its possible and what to change in your solution. Could you please help me with this? Thanks.
Thanks MFelix , this makes sense a lot. However I do not have any of the values in Columns, I have it as measure instead
Example, Volume, Margin, Unit Margin i dont have it as column, i have it as measures. So how do I have a dynamic column selections like you said.
I won't be able to create a unrelated table with all those columns as suggested in your earlier reply!
Hope i did not confuse you much!
Hi Anonymous ,
The idea is the same because in this one we exchange the column values by measures:
Totals Columns = SWITCH([Column selection];"Quantity";[Quantity total];"Value";[Value total])
As you can see in the syntax above the [Quantity total] not having a name before it means its a measure, so in your case you need to create a disconnected table with your measures names and then adjust the formula above with the measure names, be aware that if the measure have different formats you must use the format, to make the correct formatting.
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
In my model I have 12 columns. Is there a way to sum the selected columns?
