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?
