Forum Discussion

ratercero's avatar
ratercero
Icon for Helper III rankHelper III
8 years ago
Solved

Formula not showing earliest Value

Hello, 

 

Im looking to determine the first dyelot nomber produces with a certain color and Yarn lot, the formula keeps showing another dyelot number:

 

Here is the formula I used:

 

Yarn First Dye = 
CALCULATE (
    FIRSTNONBLANK ( HILAZA_ACUM[DYELOT]; HILAZA_ACUM[DYELOT] );
    CALCULATETABLE (
        HILAZA_ACUM;
        ALLEXCEPT ( HILAZA_ACUM; HILAZA_ACUM[LOTE HILAZA DEPURADO];HILAZA_ACUM[Cod. Color] );
        MIN(HILAZA_ACUM[FECHA];HILAZA_ACUM[FECHA])
    )
)

This is what I get:

 

 

I dont understand why but Ignores values that start with alfabetical caracters and it should not.

 

Please advise how to resolve.

 

Best Regards

 

RT

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi ratercero,

     

    Try this formula, please.

    Measure =
VAR earliestDate =
    CALCULATE (
        MIN ( HILAZA_ACUM[FECHA] ),
        ALLEXCEPT (
            HILAZA_ACUM,
            HILAZA_ACUM[Cod. Color],
            HILAZA_ACUM[LOTE HILAZA DEPURADO]
        )
    )
RETURN
    CALCULATE (
        MIN ( HILAZA_ACUM[DYELOT] ),
        FILTER (
            ALLEXCEPT (
                HILAZA_ACUM,
                HILAZA_ACUM[Cod. Color],
                HILAZA_ACUM[LOTE HILAZA DEPURADO]
            ),
            HILAZA_ACUM[FECHA] = earliestDate
        )
    )

    Formula_not_showing_earliest_Value

     

    Best Regards,

    Dale

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi ratercero,

     

    Try this formula, please.

    Measure =
VAR earliestDate =
    CALCULATE (
        MIN ( HILAZA_ACUM[FECHA] ),
        ALLEXCEPT (
            HILAZA_ACUM,
            HILAZA_ACUM[Cod. Color],
            HILAZA_ACUM[LOTE HILAZA DEPURADO]
        )
    )
RETURN
    CALCULATE (
        MIN ( HILAZA_ACUM[DYELOT] ),
        FILTER (
            ALLEXCEPT (
                HILAZA_ACUM,
                HILAZA_ACUM[Cod. Color],
                HILAZA_ACUM[LOTE HILAZA DEPURADO]
            ),
            HILAZA_ACUM[FECHA] = earliestDate
        )
    )

    Formula_not_showing_earliest_Value

     

    Best Regards,

    Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    If you mean first Dyelot with regards to FECHA, maybe you could use this:

     

    CALCULATE( FIRSTNONBLANK (HILAZA_ACUM [DYELOT] ) , 
FILTER ( HILAZA_ACUM,
        (HILAZA_ACUM [FETCHA] ) = MAX (HILAZA_ACUM [FETCHA] )))

    You could just modify it further to achieve what you want. You just have to get the the first data with regards to the date in the table, if that's what you meant by 'first' data. 

     

    Hope this helps!