Remove Duplicate Rows based on Condition

[StuartSmith]

I have a table with some almost identical rows apart from one row has a "Call Status" of "Call Received" or "Missed Call".  How can I remove any duplicate rows that have both these values and only keep the "Missed Call" row?

Thanks in advance

EDIT: I should add, this table is a "Combine" of 2 other tables.

[DOLEARY85]

Hi,

could you not add something simple like a filter to the page/visual on call status to only show "Missed Call"?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[StuartSmith]

Unfortuantely not, when a call is recevied, a Power App will record either 1 or 2 rows, the first row records the call and if the call is answered, thats it, no additional row, but if the call is not answered, it will record a 2nd almost identical row, but with the status of "missed call".  I only want to see 1 row per call, if a call was missed, I want to see that, but if there is no "Missed Call" row, just want to see the "Call Recevied" row.  Hope that makes sense.

[DOLEARY85]

Ah okay that makes sense, try this as a measure to create a flag:

Measure 3 =

var numofrecords = CALCULATE(COUNTROWS('Table (2)'),ALLEXCEPT('Table (2)','Table (2)'[Date],'Table (2)'[Time]))

var missedcall = CALCULATE(Max('Table (2)'[Call Status]))

return

if(numofrecords =1,1,IF(and(numofrecords = 2,missedcall = "Call Missed"), 1,0))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[StuartSmith]

Thanks, I will accept your solution shortly as that worked great.  That said, I have found an annomily with my data and normally would only expect either 1 "Call Received" or 1 "Call Received" & 1 "Call Missed", but I have notcied one entry with 1 "Call Received" and 2 "Call Missed".  

So you current solution will show both rows as they are both "Missed Call" rows.  Is there a way juts to show one row if there are more than 1 "Missed Call" rows of the same date/time?

Thanks in advance

[StuartSmith]

I presume its a "Max" function, but cant figure out where to add it 🙂

[DOLEARY85]

I suspect you're right that it will include MAX or possibly RANKX, i haven't got time to look at it today but i'll see what i can do tomorrow. if you want to include both the missed calls for now change the part of the measure  "= 2" to ">= 2" otherwise as there are 3 rows it will mark them all as 0

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[DOLEARY85]

I think the issue is going to be that both rows are identical, do you have a unique row number in the data anywhere? or could you add an index row in Power Query?

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[StuartSmith]

There is an ID coulmn pulled from the SP List, although can also add an index column from within BI if that helps.

[DOLEARY85]

Sorry about the delay, if you add an Index column in Power Query then try this:

Measure 3 =

var numofrecords = CALCULATE(COUNTROWS('Table (2)'),ALLEXCEPT('Table (2)','Table (2)'[Date],'Table (2)'[Time]))

var missedcall = CALCULATE(Max('Table (2)'[Call Status]))

return

if(numofrecords =1,1,IF(and(numofrecords = 2,missedcall = "Call Missed"), 1,IF(AND(numofrecords>=3,missedcall = "Call Missed" && MAX('Table (2)'[Index])),1,0)))

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[StuartSmith]

Thanks for getting back to me and you are helping me out, so happy to wait 🙂  I tried your code and the Max function doesnt seem to be picking up the "Max" index value.  I tried MaxA, Min, and none seem to work.  It seems to be marking them all as "1".

[DOLEARY85]

That's odd, it seems to work with my tests. Are you able to share your PBIX file?