cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Champion
Community Champion

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

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
Highlighted
Microsoft
Microsoft

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

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.

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors