Forum Discussion

Luis_Caston's avatar
Luis_Caston
Helper III
2 years ago
Solved

Calculate filtering text column by range

Hi guys!

 

I'm hesitanting:

  • I've a column [CodigoFinca] with this text values: 01,02,03,04,05,06,07,08,09,10,MIXED, ALONE.

 

I want to calculate in a range from 01 to 07 but is text not number.
I've created this formula and it works perfectly:

  • SUMX(VALUES(Cultivo), CALCULATE( [Hectáreas Final],
    USERELATIONSHIP(CabeceraCampo[FechaPlantacion], Calendario[Fecha]),
    FILTER(ALL(Calendario), Calendario[Fecha] <= MAX(Calendario[Fecha])), Cultivo[CodigoFinca] IN {"01","02","03", "04","05","06", "07"}))


Do you think that on this way is the best option?

  • Cultivo[CodigoFinca] IN {"01","02","03", "04","05","06", "07"}

Is there another way as for example:

  • Cultivo[CodigoFinca] BETWEEN {"01","08"}

Or whatever.

Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Luis_Caston ,

     

    You can modify it to the following  measure：

    Test1_Measure =
var _text=
ISERROR(VALUE(MAX('Cultivo'[CodigoFinca])))
var _if=
IF(
    _text=FALSE(),MAX([CodigoFinca]),BLANK())
return
VALUE(_if)
    Test2_Measure =
SUMX(
    FILTER(ALL('Cultivo'),
    [Test1_Measure]<=8&&[Test1_Measure]<>BLANK()),[Hectáreas Final])

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Luis_Caston ,

    I created some data:

    You can use ISERROR() and VALUE() to determine TRUE/FALSE.

    Create measure.

    Measure =
SUMX(
    FILTER(ALL('Cultivo'),
    ISERROR(
    VALUE('Cultivo'[CodigoFinca]))=FALSE()),[Hectáreas Final])

    Result:

    You can try placing the following filter condition in your formula:

    ISERROR(VALUE('Cultivo'[CodigoFinca]))=FALSE())

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • Luis_Caston's avatar
      Luis_Caston
      Helper III
      2 years ago

      Hi!!
      First of all, thank you for your support!
      Good idea 💪
      In this case would be perfect if I would want to sum all the range except "text" from "01" to "10".
      But if I only want to sum from "01" to "08"?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  Luis_Caston ,

     

    You can modify it to the following  measure：

    Test1_Measure =
var _text=
ISERROR(VALUE(MAX('Cultivo'[CodigoFinca])))
var _if=
IF(
    _text=FALSE(),MAX([CodigoFinca]),BLANK())
return
VALUE(_if)
    Test2_Measure =
SUMX(
    FILTER(ALL('Cultivo'),
    [Test1_Measure]<=8&&[Test1_Measure]<>BLANK()),[Hectáreas Final])

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Luis_Caston's avatar
      Luis_Caston
      Helper III
      2 years ago

      Thank you Anonymous!!
      Pretty amazing!
      I've adapted the formula following your criteria and works perfectly.

      "[HA codigo finca]" is yours "Test1_Meausre]". 

      SUMX(VALUES(Cultivo), CALCULATE( [Hectáreas Final], USERELATIONSHIP(CabeceraCampo[FechaPlantacion], Calendario[Fecha]), FILTER(ALL(Calendario), Calendario[Fecha] <= MAX(Calendario[Fecha])),
      FILTER(ALL(Cultivo[CodigoFinca]), [HA Codigo finca]<=7&&[HA Codigo finca]<>BLANK())))

      Thank you so much, universal and pretty simple.