Forum Discussion
Create Column Header above Measures in Matrix or delete empty columns
Hi Krexx
Instead of using a variable use the syntax within the switch measure:
SWITCH(SELECTEDVALUE(Spalten[ID]), 1, CALCULATE([H_R_Monat]), 2, CALCULATE([H_A_Monat]), 3, CALCULATE([H_Ist_Gesch_M]), 4, CALCULATE([H_P_Monat]), 5, CALCULATE([H_Abw_Monat]), 6, CALCULATE([H_in%_Monat]), 10, CALCULATE([H_R_YTD]), 11, CALCULATE([H_A_YTD]), 12, CALCULATE([H_Ist_Gesch_YTD]), 13, CALCULATE([H_P_YTD]), 14, CALCULATE([H_Abw_YTD]), 15, CALCULATE([H_in%_YTD]), 19, CALCULATE([H_P_Year]), 20, CALCULATE([H_P_Monat]) )
Hi Krexx ,
What you need to do then is to add an extra level to your MTD YTD table with the measures you need and then use the syntax in a different way.
Something similar to this:
|
ID |
Spalte | Measure |
|
1 |
Monat | Measure 1 |
| 2 | Monat | Measure 2 |
| 3 | YTD | Measure 3 |
| 4 | YTD | Measure 4 |
| 5 | YTD | Measure 5 |
Now do the following measure:
_H_R_Monat_Test =
VAR Spalte = SELECTEDVALUE(Spalten2[ID])
Return
SWITCH(TRUE(),
Spalte= 1, CALCULATE([Measure 1]),
Spalte= 2, CALCULATE([Measure 2]),
Spalte= 3, CALCULATE([Measure 3]),
Spalte= 4, CALCULATE([Measure 4]),
Spalte= 5, CALCULATE([Measure 5])
)
Add both levels to matrix hierarchy and this measure to the values.
Hi MFelix, thanks for your idea, it workes nearly perfect.
But now i am having following scenario which i cant explain. My Measure looks like this:
_H_R_Monat_Test =
Var Spalte= SELECTEDVALUE(Spalten[ID])
Return
SWITCH(TRUE(),
Spalte=1, CALCULATE([H_R_Monat]),
Spalte=2, CALCULATE([H_A_Monat]),
Spalte=3, CALCULATE([H_Ist_Gesch_M]),
Spalte=4, CALCULATE([H_P_Monat]),
Spalte=5, CALCULATE([H_Abw_Monat]),
Spalte=6, CALCULATE([H_in%_Monat]),
Spalte=10, CALCULATE([H_R_YTD]),
Spalte=11, CALCULATE([H_A_YTD]),
Spalte=12, CALCULATE([H_Ist_Gesch_YTD]),
Spalte=13, CALCULATE([H_P_YTD]),
Spalte=14, CALCULATE([H_Abw_YTD]),
Spalte=15, CALCULATE([H_in%_YTD]),
Spalte=19, CALCULATE([H_P_Year]),
Spalte=20, CALCULATE([H_P_Monat])
)When i use alle the columns from the second table in my matrix i can see all the columns perfectly.
But as soon as i remove the ID from the Columns, it looks like this:
Some values are missing, had you ever experienced the same problem?
Thanks in advance
'
Hi Krexx
Instead of using a variable use the syntax within the switch measure:
SWITCH(SELECTEDVALUE(Spalten[ID]), 1, CALCULATE([H_R_Monat]), 2, CALCULATE([H_A_Monat]), 3, CALCULATE([H_Ist_Gesch_M]), 4, CALCULATE([H_P_Monat]), 5, CALCULATE([H_Abw_Monat]), 6, CALCULATE([H_in%_Monat]), 10, CALCULATE([H_R_YTD]), 11, CALCULATE([H_A_YTD]), 12, CALCULATE([H_Ist_Gesch_YTD]), 13, CALCULATE([H_P_YTD]), 14, CALCULATE([H_Abw_YTD]), 15, CALCULATE([H_in%_YTD]), 19, CALCULATE([H_P_Year]), 20, CALCULATE([H_P_Monat]) )Hi Krexx
Just for your information this has to do with the context transiction, since you were using the Variable that is calculated before the function you need the context is given before the calculation so you need to have the ID column on the visualization, when you place the ID in within the switch function the table context is first loaded and then the ID is already consider for the calculation so the corresponding rows of each ID appear normally.
Awesome, thank you very much 🙂 !!!!!
Hi Krexx,
Can you please share a pbix file with some dummy data? It should help us clarify these scenarios and test these expressions.
How to Get Your Question Answered Quickly
Notice: please not attach sensitive data in your sample.
Regards,
Xiaoxin Sheng