cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saritart1 Frequent Visitor
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. 

Captura.PNG                                                                                                                                                                                              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,

6 REPLIES 6
Super User
Super User

Re: how to create a measure with conditional values

@saritart1

 

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

 

Example:

 

=SUMX('Tabla1',[expresion])

 

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




saritart1 Frequent Visitor
Frequent Visitor

Re: how to create a measure with conditional values

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. 
Super User
Super User

Re: how to create a measure with conditional values

@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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Community Support Team
Community Support Team

Re: how to create a measure with conditional values

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.
saritart1 Frequent Visitor
Frequent Visitor

Re: how to create a measure with conditional values

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!
 
 
tabla.PNGFIGURE 1problema.PNGFIGURE 2
Community Support Team
Community Support Team

Re: how to create a measure with conditional values

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