Forum Discussion

leroy773's avatar
leroy773
Icon for Helper II rankHelper 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
    Icon for Helper II rankHelper 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
      Icon for Helper II rankHelper 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))