Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
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.
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.
No, it doesn't work. So it should be:
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.
Thank you, it works!
@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))
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |