Forum Discussion

GeorgW's avatar
GeorgW
Helper II
4 years ago
Solved

Final Status Code

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?

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    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.

7 Replies

Replies have been turned off for this discussion
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    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's avatar
      GeorgW
      Helper II
      4 years ago

      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's avatar
        v-xiaotang
        Community Support
        4 years ago

        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.