Forum Discussion

leroy773's avatar
leroy773
Helper II
8 years ago
Solved

Remove rows based on duplicate value in column

Looking for a DAX formula to remove rows based on duplicate serial numbers.  I currently have a data table with multiple rows.  Some of the rows have the same serial number.  I would like to count th...
Need Help
  • Rfranca's avatar
    Rfranca
    8 years ago

    leroy773 mickeydjw

     

    Oops!

    Got it,

     

     

    Tt_SerialNumber_Replace = CALCULATE(
             DISTINCTCOUNT(Planilha1[serial Number]);
                   filter(ALL(Planilha1);Planilha1[Part Replace]=1))

     

     

    Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
     
    Best Regards,
    Rfranca

leroy773's avatar
leroy773
Helper II
8 years ago

Thanks that helped, but still having trouble.  The distinctcount showed how many rows had a unique serial number, but would like to count the number of rows with a unique serial number when a true/false condition is met in a different columnt on the table.  

  • leroy773's avatar
    leroy773
    Helper II
    8 years ago

    Sample data below, looking for the dax formula so the rows that are counted or summed equals 5.  Only count the serial number if part replace equals 1 and only count the serial number one time.

     

    serial NumberPart Replace
    11
    11
    10
    11
    21
    21
    31
    40
    40
    41
    51
    60
    60
    • mickeydjw's avatar
      mickeydjw
      Frequent Visitor
      8 years ago

      Measure = CALCULATE(DISTINCTCOUNT('Table'[serial Number]),FILTER('Table','Table'[Part Replace]=1))

    • Rfranca's avatar
      Rfranca
      Resolver IV
      8 years ago

      leroy773 mickeydjw

       

      Oops!

      Got it,

       

       

      Tt_SerialNumber_Replace = CALCULATE(
               DISTINCTCOUNT(Planilha1[serial Number]);
                     filter(ALL(Planilha1);Planilha1[Part Replace]=1))

       

       

      Please try and test using it. And please mark the right answer as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
       
      Best Regards,
      Rfranca