cancel
Showing results for 
Search instead for 
Did you mean: 
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/

 



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

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/

 



!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!

thanks, the parameter option was the solution for me. 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors