how to create a measure with conditional values

[saritart1]

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,

[v-yulgu-msft]

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]

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]

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

[Vvelarde]

@saritart1

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

Example:

=SUMX('Tabla1',[expresion])

Regards

Victor

[saritart1]

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]

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