Forum Discussion

Krexx's avatar
Krexx
Helper I
5 years ago
Solved

Create Column Header above Measures in Matrix or delete empty columns

Hello together, i will try it simple 🙂 I would like to achieve this  So now when i am using following code i am losing the measure under the headername "Month" & "YTD", so i cant...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

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

     

     

     

MFelix's avatar
MFelix
Super User
5 years ago

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.

 

Krexx's avatar
Krexx
Helper I
5 years ago

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

 


'