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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
avanroij
Helper I
Helper I

dynamic columns with switch

I want to use dynamic columns based on a slicer.

I have a dataset with 2 measures (M1_SUM and M2_SUM), 2 correspoding size columns (M1_SIZE and M2_SIZE) and 1 dimension (DIM):

DIMM1_SUMM2_SUMM1_SIZEM2_SIZE
A             10             15ML
A               9               8MM
A             18             17LL
B             10               5MM
B             79             14LM
total          126             59  

 

I use a slicer to select one of the 2 measures (M1_SUM or M2_SUM) by using the SWITCH formula and a custom table (name: Tabel):
code name
1 M1
2 M2

formulas (measures):
selection = selectedvalue(Tabel[code],1)
Selected M = switch([selection], 1, sum(Blad1[M1_SUM]),2 ,sum(Blad1[M2_SUM]),3,sum(Blad1[M3_SUM]))

So,
when I select M1 in the slicer, the MEASURE column shows the M1_SUM total value (126)
when I select M2 in the slicer, the MEASURE column shows the M2_SUM total value (59) (as shown in the print below)

so far so good.

 

switch.jpg

 

But what I cannot achieve:
* when I select M1 in the slicer, I want to show the M1_SIZE column values (next to the M1_SUM values)
* when I select M2 in the slicer, I want to show the M2_SIZE column values (next to the M2_SUM values)

desired output (in the same table):
1) select M1 in the slicer:

DIMMEASURESIZE
A              10M
A                9M
A              18L
B              10M
B              79L


2) select M2 in the slicer:

DIMMEASURESIZE
A              15L
A                8M
A              17L
B                5M
B              14M

I tried and if then else (if Tabel.name = M1 then use M1_SIZE else M2_SIZE), but this is not working. perhaps because I want to use a measure to switch to a column.

 

How can I achieve this?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@avanroij , If you want to change axis/ change un summarized value. You need use bookmarks or unpivot these columns

bookmarks 

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

 

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/

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@avanroij , If you want to change axis/ change un summarized value. You need use bookmarks or unpivot these columns

bookmarks 

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

 

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/

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

thanks, the parameter option was the solution for me. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

Top Kudoed Authors