Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom Column to show duplicates with condition

As per screenshot, I need a custom column to show duplicates on the JobID+Ticket column, based on the following conditions:

  • if JobID+Ticket contains duplicate, return "Yes" else "No"
  • if TICKET_NBR is blank, return "No"

Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    Try this measure:

    Column =
VAR _Disc =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[JOBID+Ticket] = EARLIER ( 'Table'[JOBID+Ticket] )
        )
    )
RETURN
    IF ( ISBLANK ( 'Table'[TICKET_NBR] ) || _Disc = 1, "No", "Yes" )

     

    Output:

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous ,

    a new column=

    var _cnt = countx(filter(Table,[JobID+Ticket] =earlier([JobID+Ticket]) ),[JobID+Ticket])+0

    return

    if(isbalnk([TICKET_NBR]) && _cnt <=1 , "No", "Yes")

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak , thanks but it doesn't look right as its only showing all "Yes"

      VahidDM, any suggestions please?

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    Try this measure:

    Column =
VAR _Disc =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[JOBID+Ticket] = EARLIER ( 'Table'[JOBID+Ticket] )
        )
    )
RETURN
    IF ( ISBLANK ( 'Table'[TICKET_NBR] ) || _Disc = 1, "No", "Yes" )

     

    Output:

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      VahidDM, perfect that worked. Thank you.