Forum Discussion

saritart1's avatar
saritart1
Frequent Visitor
7 years ago

how to create a measure with conditional values

 

Hello everyone,

 

I am struggling with the creation oaf  measure. I hope you can help me.

 

I have a table with some measures that are binary (1,0) as follow:

 

Intensidad alta = if(asistencia[total asistencia por estudiante]>=2/3;1;0)
Intensidad baja = if(asistencia[total asistencia por estudiante]<1/3;1;0)
Intensidad media = if(and(asistencia[total asistencia por estudiante]>=1/3;asistencia[total asistencia por estudiante]<2/3);1;0)
 
Now, I am trying to create a new variable in a new table that has only 3 values and an associated measure for each one. On the picture you can see the 3 values. 

                                                                                                                                                                                              Now, I want my new measure to sum on of each of the measures above for each value of the "Medida" column, as follows

 

Medida          New Measure

Baja                 SUMX(intensidad Baja])

Media             SUMX(intensidad Media])

Alta                SUMX(intensidad Alta])

 

This is what I did, but it didn´t worked. Hope you can use it to better understand my problem and help me to find out the solution:

 

New Measure = IF(CALCULATE( table1[Medida] = "Baja" ; SUMX(intensidad Baja]); IF( table1[Medida] = "Media"; SUMX(intensidad Media]); SUMX(intensidad Alta]))))

 

Hope you can help me and thank you very much.

 

Regards,

Need Help

6 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    7 years ago

    saritart1

     

    The SUMX that are you using you need to specify the table to iterate.

     

    Example:

     

    =SUMX('Tabla1',[expresion])

     

    Regards

     

    Victor

    • saritart1's avatar
      saritart1
      Frequent Visitor
      7 years ago

      Thank you very much Vvelarde. 

       

      my new expression is the following:

      New Measure = IF( CALCULATE( table1[Medida] = "Baja" ; SUMX(asistencia; asistencia[Intensidad baja]); IF( table1[Medida] = "Media"; SUMX(asistencia; asistencia[Intensidad media]); SUMX(asistencia; asistencia[Intensidad alta]))))
       
      Nevertheless it doesn`t work. It says that IF mus have at least 2 values. I believe mine has 3 so it shouldn`t be a problem. 
      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        7 years ago

        saritart1

         

        Table1 is a table that you in a slicer?

         

        Is answer is yes, use this:

         

        Measure=SWITCH(SELECTEDVALUE[Table1[Medida]),"Baja", ACCION1,"Media",ACCION2, "Alta", ACCION3)

         

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi saritart1,

     

    Please try this measure:

     

    New Measure =
IF (
    SELECTEDVALUE ( table1[Medida] ) = "Baja",
    SUMX ( ALLSELECTED ( asistencia ), [intensidad Baja] ),
    IF (
        SELECTEDVALUE ( table1[Medida] ) = "Media",
        SUMX ( ALLSELECTED ( asistencia ), [intensidad Media] ),
        SUMX ( ALLSELECTED ( asistencia ), [intensidad Alta] )
    )
)


    Best regards,

    Yuliana Gu

    • saritart1's avatar
      saritart1
      Frequent Visitor
      7 years ago

      Thank you very much v-yulgu-msft, it worked! 

       

      One last question, if I want to include a contrain (to do it by another column) how should I do it? I did this, but it doesn´t work.

       

      New Measure =
      CALCULATE(IF (
      SELECTEDVALUE ( table1[Medida] ) = "Baja";
      SUMX ( asistencia; asistencia [Intensidad baja] );
      IF (
      SELECTEDVALUE ( table1[Medida] ) = "Media";
      SUMX ( asistencia; asistencia[Intensidad media] );
      SUMX ( asistencia ; asistencia[intensidad Alta] )
      )
      );ALLEXCEPT(asistencia; asistencia[Nombre];asistencia[Clase]))
       
      As you can see in FIGURE 1, there is no value for "Baja" but, when I apply this expression, somehow, there are values for "Bajo "(FIGURE 2). I believe is because I need to filter the SUMX by "NOMBRE" and that is why I tried, but I think I did it on the wrong way.
       
      Thank you very much!
       
       
      FIGURE 1FIGURE 2
      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi saritart1,

         

        Please try this:

         
        New Measure =
IF (
    SELECTEDVALUE ( table1[Medida] ) = "Baja";
    SUMX (
        ALLEXCEPT ( asistencia; asistencia[Nombre]; asistencia[Clase] );
        asistencia[Intensidad baja]
    );
    IF (
        SELECTEDVALUE ( table1[Medida] ) = "Media";
        SUMX (
            ALLEXCEPT ( asistencia; asistencia[Nombre]; asistencia[Clase] );
            asistencia[Intensidad media]
        );
        SUMX (
            ALLEXCEPT ( asistencia; asistencia[Nombre]; asistencia[Clase] );
            asistencia[intensidad Alta]
        )
    )
)

         

         
        Best regards,
        Yuliana Gu