cancel
Showing results for
Did you mean: Frequent Visitor

## 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

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,

6 REPLIES 6  Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Frequent Visitor

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 1 FIGURE 2  Microsoft

Hi @saritart1,

```New Measure =
IF (
SELECTEDVALUE ( table1[Medida] ) = "Baja";
SUMX (
ALLEXCEPT ( asistencia; asistencia[Nombre]; asistencia[Clase] );
);
IF (
SELECTEDVALUE ( table1[Medida] ) = "Media";
SUMX (
ALLEXCEPT ( asistencia; asistencia[Nombre]; asistencia[Clase] );
);
SUMX (
ALLEXCEPT ( asistencia; asistencia[Nombre]; asistencia[Clase] );
)
)
)```

Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Community Champion

@saritart1

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

Example:

=SUMX('Tabla1',[expresion])

Regards

Victor

Lima - Peru Frequent Visitor

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.  Community Champion

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

Lima - Peru   