Solved: dynamic columns with switch

avanroij · ‎04-22-2021

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

DIM	M1_SUM	M2_SUM	M1_SIZE	M2_SIZE
A	10	15	M	L
A	9	8	M	M
A	18	17	L	L
B	10	5	M	M
B	79	14	L	M
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:

DIM	MEASURE	SIZE
A	10	M
A	9	M
A	18	L
B	10	M
B	79	L

2) select M2 in the slicer:

DIM	MEASURE	SIZE
A	15	L
A	8	M
A	17	L
B	5	M
B	14	M

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?