cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lidou Regular Visitor
Regular Visitor

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

Accepted Solutions
RobbeVL Established Member
Established Member

Re: Conditionnal displaying measure

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

Highlighted
Lidou Regular Visitor
Regular Visitor

Re: Conditionnal displaying measure

Hello !!

Thank you for your help. That works well.

Thanks a lot !!!


@Lidou 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 Established Member
Established Member

Re: Conditionnal displaying measure

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

Lidou Regular Visitor
Regular Visitor

Re: Conditionnal displaying measure

Hello Robbe

 

Thank you for your help.

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

 

Thanks a lot

Highlighted
Lidou Regular Visitor
Regular Visitor

Re: Conditionnal displaying measure

Hello !!

Thank you for your help. That works well.

Thanks a lot !!!


@Lidou 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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,804)