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 

 

You need to place both measure in the same switch function:

_H_R_Monat_Test =
VAR Spalte = SELECTEDVALUE(Spalten2[Spalte])
Return
SWITCH(TRUE(),Spalte="Month", CALCULATE([H_R_Month]), Spalte="YTD", CALCULATE([H_R_YTD]))

 

Krexx's avatar
Krexx
Helper I
5 years ago

I tried this already, but i have the problem that i have measures that only need to be displayed in YTD.

When i am creating then the following measure: 

Test_Value =
VAR Spalte = SELECTEDVALUE(Spalten2[Spalte])
Return
SWITCH(TRUE(),Spalte="YTD", CALCULATE([Test_Value]))


This value is also displayed under Month...the easiest way would be to remove than these columns but i couldnt find any solution to this until now...

  • 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

       


      '

       

      • MFelix's avatar
        MFelix
        Super User
        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])
)