Remove deplicate rows based on lowest number

[Anonymous]

I am struggling to get this working.

This is a small portion of my dataset. My dataset only contains TicketId, CommentId (not shown here, not really important), and CommentDuration. CommentDuration is a calculated column.

What I am trying to achieve is to remove rows with the same TicketId, keeping only the lowest CommentDuration. How can I do this? Preferably as a DAX measure...

Thanks in advance

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Kindly check below results:

Measure = var a = CALCULATE(MIN('Table (2)'[CommentDuration]),ALLEXCEPT('Table (2)','Table (2)'[Ticketid]))
return 
IF(MAX('Table (2)'[CommentDuration])=a,a,BLANK())

 

[amitchandak]

@Anonymous , Try a measure like these

sumx(summarize(table, table[TicketId],"_1",min(CommentDuration )),[_1])

or

sumx(values(table[TicketId]),min(CommentDuration ))

[Fowmy]

@Anonymous 

Create a new table with the following code (Go to Modeling ab, 'under Calculation, New Table

New Table = 

SUMMARIZECOLUMNS(
    TICKET[TicketID],
    "MinDur",
    MIN(TICKET[CommetnDuration])
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn