Final Status Code

[GeorgW]

Hello,
I have a table (phone call log) with many entries (each forwarding of the phone call creates a new row). To determine the final status (did the caller reach someone or not), I have an "ID" for each call, i.e. this "ID" is the same for several rows.
To determine the final "StatusCode" (successful or not), I read the StatusCode of the last time. However, it can happen that two entries were created at the same time, so I have two different StatusCodes with the same ID. Therefore I want to define in a new column "FinalStatusCode" that if there are two different StatusCodes with the same "ID" and one of them has the value "200", in the "FinalStatusCode" the value "200" should be transmitted, otherwise the value "100". But only in the last or one of the columns with the same ID.

Was this understandable or is this possible?

[v-xiaotang]

Hi @GeorgW 

try this

FinalStatusCode = CALCULATE(MAX('Table'[StatusCode]),ALLEXCEPT('Table','Table'[ID]))

result

OR

FinalStatusCode = 
var _maxCode= CALCULATE(MAX('Table'[StatusCode]),ALLEXCEPT('Table','Table'[ID]))
return IF('Table'[StatusCode]=_maxCode,_maxCode,BLANK())

Best Regards,

Community Support Team _Tang

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

[GeorgW]

Thank you very much. No, unfortunately it is not sufficient. It should only take into account the rows that also have the same time at "Time". I have not expressed it precisely enough.

[v-xiaotang]

Hi @GeorgW 

then try 

FinalStatusCode = CALCULATE(MAX('Table'[StatusCode]),ALLEXCEPT('Table','Table'[ID],'Table'[Time]))

if this does not solve your question, could you show us a sample? so that we can work on it further.

Best Regards,

Community Support Team _Tang

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

[GeorgW]

No, it doesn't work. So it should be:

[v-xiaotang · Accepted solution]

Hi @GeorgW 

try this

Measure = 
var _table=ALLEXCEPT('Table','Table'[ID])
var _maxtime=CALCULATE(MAX('Table'[time]),_table)
var _code=200
var _mincode=CALCULATE(MIN('Table'[StausCode]),FILTER(_table,'Table'[time]=_maxtime))
return
SWITCH(TRUE(),
MIN('Table'[time])=_maxtime&&MIN('Table'[StausCode])=_code,_code,
MIN('Table'[time])=_maxtime&&MIN('Table'[StausCode])=_mincode,100)

result

Best Regards,

Community Support Team _Tang

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

[GeorgW]

Thank you, it works!

[amitchandak]

@GeorgW , Based on what I got, Check if these measures can help

lastnonbalnkvalue(Table[ID], max(Table[FinalStatusCode]))

calculate(lastnonbalnkvalue(Table[ID], max(Table[FinalStatusCode])), allexcept(Table, Table[ID))