Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

SelectColumns.gif

 

See attach PBIX.

 

Regards,

MFelix 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

26 REPLIES 26
wlad
Frequent Visitor

In my model I have 12 columns. Is there a way to sum the selected columns?

Hi @wlad 

How are you selecting the columns?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



wlad
Frequent Visitor

Hi,

However I select with a slicer or directy in a matrix the sum is wrong

Sum is correct only if I select one column

Hi @wlad,

 

How do you have everything setup? is it diferrent columns? If they are different columns the totals on the matrix will not sum the columns since each field is unique.

 

For this you have two alternatives, create an unrelated table with the name of the columns you want to use and then adding a switch statement you can calculate each value individually and sum the total of all of them in another total value. Unpivo your columns and use the value for the columns on your matrix.

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



wlad
Frequent Visitor

Hi @MFelix ,

My data model is similar to "Column Selection Test.pbix " I found in an earlier post and since the table is built using summarizecollumns I can't unpivot it.

So the second solution is suitable for me:
"Create an unrelated table with the name of the columns you want to use and then adding a switch statement you can calculate each value individually and sum the total of all of them in another total value"

 

Thank you for your help

MFelix
Super User
Super User

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:

SelectColumns.gif

 

See attach PBIX.

 

Regards,

MFelix 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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. 

Anonymous
Not applicable

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. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ttt
Regular Visitor

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

ttt_0-1608152260325.png

 

After I added month columns

ttt_1-1608152402459.png

 

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. 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ttt
Regular Visitor

@MFelix Thank you so much! It worked. I thought the drill down buttons were only for multiple rows.

Hi @ttt ,

 

In the matrix works for columns and rows if you have hierarchy on both.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ttt
Regular Visitor

@MFelix got it. Thanks so much! One last question, I want the users to be able to sort the matrix by value in each column e.g sorting rolling forecast amounts in Dec from largest to smallest, sorting new budget amounts in Nov from smallest to largest. Is it possible to do so?

ttt_0-1608254771556.png

 

Thanks for all your help! Appreciate it.

 

Hi @ttt ,

 

If the users click on the header of the column they are abble to sort the information by that column, to have the sorting by more than one column they need to do SHIFT + CLICK on the other column headers.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



ttt
Regular Visitor

Hi @MFelix ,

 

That usually works for me when I don't have data in the Columns field. When I have Columns data clicking on a column header only put a spotlight on that column.

 

ttt_0-1608309792962.png

 

@ttt  I belive you can only sort by column, not by values/measure.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hey @MFelix ,

 

I tried that and unfortunately didn't work for me. I used the same formulas as you did, same field names, but I get a matrix like this.

Undercover95_0-1599044335464.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.