Calculated column value based on another column and rows

Felizzpe — Fri, 06 Dec 2024 19:13:20 GMT

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 Order	Location	Order Group	Order Status
1	Finished	5100	Not Ready Since PO Nº 4 is not finished yet.
2	Finished	5100	Not Ready Since PO Nº 4 is not finished yet.
3	Finished	5100	Not Ready Since PO Nº 4 is not finished yet.
4	Painting	5100	Not Ready Since PO Nº 4 is not finished yet.
5	Finished	6200	ready for dispatch
6	Finished	6200	ready for dispatch
7	Finished	6200	ready for dispatch
8	Finished	6200	ready for dispatch
9	Finished	6200	ready for dispatch
10	Finished	6200	ready for dispatch

Anyone knows if this is possible?

Thanks in advance!!

Re: Calculated column value based on another column and rows

FreemanZ — Fri, 06 Dec 2024 21:48:59 GMT

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

Re: Calculated column value based on another column and rows

Sahir_Maharaj — Tue, 10 Dec 2024 16:45:26 GMT

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

topic Re: Calculated column value based on another column and rows in DAX Commands and Tips

Calculated column value based on another column and rows

Re: Calculated column value based on another column and rows

Re: Calculated column value based on another column and rows