Forum Discussion

Felizzpe's avatar
Felizzpe
New Member
1 year ago
Solved

Calculated column value based on another column and rows

Greetings, 

I'm trying to make a calculated column that shows the production order status based on the status from all other production orders of the same group. (Production orders have groups, so they can be dispached all together after everyone is ready) 

 

I tried everything that I know, but no success. Here is the last DAX code that I've tried: 
Order Status = 

VAR OrderGroup = [Grupo_Faturamento]
VAR Location = "READY FOR DISPATCH"
VAR OrdersThatArentFinished =
CALCULATE(
    COUNTROWS(
        FILTER(
            ProcessoAtualReal, -- production order tables
            ProcessoAtualReal[Grupo_Faturamento] = OrderGroup --producton order groups of dispatch
            && ProcessoAtualReal[Processo_Atual_Desc] <> Location --production order location name, like "READY FOR DISPATCH"
        )
    )
) > 0

RETURN
IF( --cheking if there is procution order without grup, is this case, leave it blank
    ISBLANK( OrderGroup ),
    BLANK(),
    IF(
        OrdersThatArentFinished,
        "NOTREADY",
        "READY FOR DISPACH"
    )
)


Here is the result that I'm looking for: 
If all orders are ready, the status should be "ready for dispatch", If one of the orders on the same group isn't ready, the status should be "not ready" for all orders of the same group, since they need to be dispatched together: 

Production OrderLocationOrder GroupOrder Status
1Finished5100Not Ready Since PO Nº 4 is not finished yet.
2Finished5100Not Ready Since PO Nº 4 is not finished yet.
3Finished5100Not Ready Since PO Nº 4 is not finished yet.
4Painting5100Not Ready Since PO Nº 4 is not finished yet.
5Finished6200ready for dispatch
6Finished6200ready for dispatch
7Finished6200ready for dispatch
8Finished6200ready for dispatch
9Finished6200ready for dispatch
10Finished6200ready for dispatch

Anyone knows if this is possible? 

Thanks in advance!!
  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi Felizzpe ,

     

    try like:

    column =

    VAR _list =

    CALCULATETABLE(

        VALUES(data[Location]),

        ALLEXCEPT(data, data[Order Group])

    )

    VAR _result =

    IF(_list = "Finished", "Ready", "Not ready")

    RETURN _result

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi Felizzpe ,

     

    try like:

    column =

    VAR _list =

    CALCULATETABLE(

        VALUES(data[Location]),

        ALLEXCEPT(data, data[Order Group])

    )

    VAR _result =

    IF(_list = "Finished", "Ready", "Not ready")

    RETURN _result

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    Hello Felizzpe,

     

    Can you please try this approach:

    Order Status = 
VAR CurrentGroup = ProcessoAtualReal[Grupo_Faturamento]
VAR IsAnyOrderNotReady =
    CALCULATE(
        COUNTROWS(
            FILTER(
                ProcessoAtualReal,
                ProcessoAtualReal[Grupo_Faturamento] = CurrentGroup &&
                ProcessoAtualReal[Processo_Atual_Desc] <> "READY FOR DISPATCH"
            )
        )
    ) > 0
RETURN
IF(
    ISBLANK(CurrentGroup),
    BLANK(),
    IF(
        IsAnyOrderNotReady,
        "NOT READY",
        "READY FOR DISPATCH"
    )
)