Calculate filtering text column by range

[Luis_Caston]

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.

[Anonymous · Accepted solution]

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]

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.

[Anonymous]

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]

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"?