Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove deplicate rows based on lowest number

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

Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

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

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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

     

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try a measure like these

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

    or

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

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

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