Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Conditionnal displaying measure

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:

 

ClassRoomStudentMeasure
ATom5
AFrank3
AStephane6
BFlorence2
BLaure4
CPhilippe18

 

 

->The Second one is like a Parameter Table:

ClassRoomMinMeasureMaxMesure
A1012
B710
C1416



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:

ClassRoomMeasure
ClassRoom A12
   Frank3
   Stephane6
   Tom3
ClassRoom B6
   Florence2
   Laure4
ClassRoom C18
   Philippe18

 

And I need to display a Matrix like this:

 

ClassRoomMeasure
ClassRoom A12*
   Frank3
   Stephane6
   Tom3
ClassRoom B6**
   Florence 
   Laure 
ClassRoom C18
   Philippe18

 

 

Do you know to I could implement it ??

 

Thank you for your help.

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS
RobbeVL
Impactful Individual
Impactful Individual

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

Capture3.PNG

 

 

Hope this helps! 

 

Robbe

View solution in original post

Anonymous
Not applicable

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


 

View solution in original post

3 REPLIES 3
RobbeVL
Impactful Individual
Impactful Individual

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

Capture3.PNG

 

 

Hope this helps! 

 

Robbe

Anonymous
Not applicable

Hello Robbe

 

Thank you for your help.

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

 

Thanks a lot

Anonymous
Not applicable

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


 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.