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

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/

 

thanks, the parameter option was the solution for me. 

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.