Forum Discussion

avanroij's avatar
avanroij
Helper I
5 years ago
Solved

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.

 

 

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?

2 Replies