How to build dynamic columns within a matrix in Desktop?

[Anonymous]

Dear All,

I am seeking for your support. I am using a matrix within Power BI Desktop, containing fields from a table and some measures (calculated values, e.g. price). I want to make the columns more dynamic, which means, to include a slicer to show or to hide columns. 

Below you will find an simplified example: two columns with a value per m2 (columns A and B); two columns with a price (columns C and D) and two columns with a  volume value (columns E and F) . I would like to create a slicer with three options: 'm2', ‘price’ and ‘volume value’. When you select the option 'm2', you only should see column A and B. Once you select the option 'total value' you only should see column C and D and once you select option ‘volume value’ you should only see columns E and F.

Beneath picture shows: complete matrix and how it should look like when I select one of the slicer variables.  Thanks in advance for any suggestion. Also another solution would be helpful, to achieve the same!

Thanks and regards,

[AllisonKennedy]

Do you already have the individual measures? If so, you can create a new unrelated table that has the values for: 

Select Measure to View:

Volume/Value

Price

m2

Then for each column create a measure:

ColumnA = 
SWITCH( SELECTEDVALUE(Slicer[Select Measure to View], "m2")

, "m2" , [m2A]

, "volume", [volumeA]

, "Volume/Value", [Volume/ValueA]

)

Then do the same for ColumnB using the B measures.

Note the second argument in the SELECTEDVALUE function is the default- in case they pick too many options or don't make a selection - what should they see?

[massar2000]

Hi,

you can create two buttons. one to hide and the other to show columns. watch the below video 

https://www.youtube.com/watch?v=GnPuU3X74SY&list=LL&index=8

Regards,

Majed

[Jihwan_Kim]

Hi, @Anonymous 

Please correct me if I wrongly understood your question.

If "m2" /"price" / "volume value" are measures, then try to follow the below steps.

1. create a one-coloumn-table:   MeasureSlicerTable = {"m2", "price","volume value"}

2. create a measure:

Selected Measure =

Switch (True(),

selectedvalue ('MeasureSlicerTable'[Value]) = "m2", [m2],

selectedvalue ('MeasureSlicerTable'[Value]) = "price", [price],

selectedvalue ('MeasureSlicerTable'[Value]) = "volume value", [volume value] )

If it is OK with you, please share your sample pbix file's link here, then I can try to come up with more accurate measures.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[Anonymous]

Thanks for your reply. How can I share a sample pbix file? 

[Jihwan_Kim]

Hi, @Anonymous 

You can save your sample pbix file in Onedrive, Googledrive, or Dropbox, and you can share the link here.

Thank you.

[Anonymous]

Please find herewith a link: https://www.dropbox.com/s/gmqlatrarpiujud/Test.pbix?dl=0

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

please check the link down below if it is OK.

https://www.dropbox.com/s/bqpan2i92jd3u2o/Test.pbix?dl=0 

[Anonymous]

Thanks! I will check later, since I first need to install a latest version of Power BI desktop, since I cannot open this file with my current version. 

[Anonymous]

Thanks! This seems to work perfectly! Really helpful!

I guess formatting of the different column values (decimals, etc ) I can manage within the measure 'Slicer Measure Select?

[Jihwan_Kim]

@Anonymous 

Yes, you can manage to change the format by selecting "Slicer Measure Select" measure.

Thank you.

[Anonymous]

Thanks Jihwan-Kim for this suggestion. 

However I am wondering since the 'Slicer Measure Select' contains different values, I could not manage one single format. I want to show values with zero, one and two decimals. I tried ROUND function, following number of decimals, but this failed. Curren;y it looks like this:

1 Slicer Measure Select =

2 SWITCH( SELECTEDVALUE(Tbl_SlicerMeasure[Measures]),

3 "Budget Volume / m2 (in kg / pieces)", [Budget Volume / m2 (in kg / pieces)], --> with zero decimal

4 "Budget - Volumes (in kg / pieces)", SUM(Qr_Creeer_Overview_Omzet_Raw_Data[Budget - Volumes (in kg / pieces)]), --> with zero decimals

5 "Budget Sales / m2 (in €)", [Budget omzet / m2 (in €)],  --> with two decimals

6 "Difference Sales per m2 (in %)", [Difference Sales per m2 (in %)], --> with one decimals

blank())

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

One way to solve this is to write like below.

The below is just an example, and you can change and re-write the type of the format inside the measure.

Slicer Measure Select =

SWITCH( SELECTEDVALUE(SlicerMeasure[measures]),

"Actual sales per m2", FORMAT([Actual sales per m2],"€ #,##0.00"),

"Budget sales per m2", [Budget sales per m2],

"Actual volumes (in kg)", FORMAT( SUM(Tbl_Data_2[Actual volumes (in kg)]), " #,##0.00 KG"),

"Budget volumes (in kg)", SUM(Tbl_Data_2[Budget volumes (in kg)]),

"Actual sales (in €)", SUM(Tbl_Data_2[Actual sales (in €)]),

"Budget sales (in €)", SUM(Tbl_Data_2[Budget sales (in €)]),

"Actual price (in €)", FORMAT([Actual price (in €)],"€ #,##0.00"),

"Budget price (in €)", [Budget price (in €)],

blank())

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Thanks Jihwan Kim! This is what I need. I will play a bit with the format inside. really helpful.

Last question...;)  

How could I order the columns in the output (when I am using the measure)? I wrote a sequwence in the measure, but it seems it's sorted on alphabet. Should I better with numbering the columns in the measure, so to get it ordered in the way I want?

Thanks!

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thank you for your feedback.

Yes, you are right. The sort-order column has to be created, and you can use this to sort the measure name in the slicer, not by alphabetical order, but by sort-number-order.

Thank you.

[Anonymous]

Thanks a lot Jihwan Kim. this was really helpful!

[amitchandak]

@Anonymous , for the column, you have a different approach

Dynamically change chart axis in Power BI
https://www.youtube.com/watch?v=6jeSIRpjv0M
https://datamonkeysite.com/2020/10/22/change-dimension-dynamically-using-parameter-in-powerbi/

For measure, you can slicer or calculation group

measure slicer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

calculation groups
https://www.youtube.com/watch?v=vlnx7QUVYME

https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/