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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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):

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]))

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.




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:

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

2) select M2 in the slicer:

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

I tried and if then else (if = 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?

Super User
Super User

Super User
Super User

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



Dynamically change chart axis in Power BI


thanks, the parameter option was the solution for me. 

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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