cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saritart1
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
v-yulgu-msft
Microsoft
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.

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

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

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors