Conditionnal displaying measure

[Anonymous]

Hi All,

I hope you are doing well.

I need your help. 

I am trying to create a matrix report and I have 2 tables.

-> The first one is my fact table like this:

ClassRoom	Student	Measure
A	Tom	5
A	Frank	3
A	Stephane	6
B	Florence	2
B	Laure	4
C	Philippe	18

->The Second one is like a Parameter Table:

ClassRoom	MinMeasure	MaxMesure
A	10	12
B	7	10
C	14	16

And I Need to create a matrix report with these business rules:

- If MesureForClassRoom  < MinMeasure

    Then " In the top level (ClassRoom Level) " : Add "**" after the measure

             "In the leaf Level (Student Level) ": Don't display measure

- If Mesure ForClassRom between MinMeasure and MaxMeasure

    Then " In the top level (ClassRoom Level) " :  Add "*" after the measure

             "In the leaf Level (Student Level) ":  Display Measure

- If Mesure ForClassRom > MaxMeasure

    Then " In the top level (ClassRoom Level) " :  DisplayMeasure

             "In the leaf Level (Student Level) ":  Display Measure

For Now my Matrix is like this:

ClassRoom	Measure
ClassRoom A	12
Frank	3
Stephane	6
Tom	3
ClassRoom B	6
Florence	2
Laure	4
ClassRoom C	18
Philippe	18

And I need to display a Matrix like this:

ClassRoom	Measure
ClassRoom A	12*
Frank	3
Stephane	6
Tom	3
ClassRoom B	6**
Florence
Laure
ClassRoom C	18
Philippe	18

Do you know to I could implement it ??

Thank you for your help.

[RobbeVL · Accepted solution]

Hi there! 

I created the measure below as followed:

MeasureCalc = 
VAR
    Me = SUM(Dim[Measure])
VAR
    var2 =  CALCULATE(SUMX(SUMMARIZE(Dim; Dim[ClassRoom]; "classsum"; SUM(Dim[Measure]));[classsum]);ALL(Dim[Student]))
RETURN

IF(ISFILTERED(Dim[Student]) = FALSE();
    IF(Me < SUM(Param[MinMeasure]) ; 
        Me&"**" ; 
        IF(AND(Me > SUM(Param[MinMeasure]); Me < SUM(Param[MaxMesure])); 
            Me&"*"; 
            IF(Me > SUM(Param[MaxMesure]) ; 
                Me;
                "what if  =  min or max measure?")));

     IF(var2 > SUM(Param[MinMeasure]) ; 
        Me ; 
        BLANK()
     )
)

I changed your sample data a little, so it would actually work !  (5+3+6 <> 12  😉   ) 

Hope this helps! 

Robbe

[RobbeVL · Accepted solution]

Hi there! 

I created the measure below as followed:

MeasureCalc = 
VAR
    Me = SUM(Dim[Measure])
VAR
    var2 =  CALCULATE(SUMX(SUMMARIZE(Dim; Dim[ClassRoom]; "classsum"; SUM(Dim[Measure]));[classsum]);ALL(Dim[Student]))
RETURN

IF(ISFILTERED(Dim[Student]) = FALSE();
    IF(Me < SUM(Param[MinMeasure]) ; 
        Me&"**" ; 
        IF(AND(Me > SUM(Param[MinMeasure]); Me < SUM(Param[MaxMesure])); 
            Me&"*"; 
            IF(Me > SUM(Param[MaxMesure]) ; 
                Me;
                "what if  =  min or max measure?")));

     IF(var2 > SUM(Param[MinMeasure]) ; 
        Me ; 
        BLANK()
     )
)

I changed your sample data a little, so it would actually work !  (5+3+6 <> 12  😉   ) 

Hope this helps! 

Robbe

[Anonymous]

Hello Robbe

Thank you for your help.

I'll test this solution and I will you my feedback.

Thanks a lot

[Anonymous · Accepted solution]

Hello !!

Thank you for your help. That works well.

Thanks a lot !!!

---

@Anonymous wrote:

Hello Robbe

Thank you for your help.

I'll test this solution and I will you my feedback.

Thanks a lot

---