Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Don't know how to start with this duplicate values problem

When I am trying to sum up the count I get 2 for John on Feb 18th but actually want that value to be 1. Is there a way to make it so that the first date comes out as 1 on the NoDupCount column and then 0 for any of the other counts that fall on that date. Looking for a calculated column solution and have looked everywere online and couldn't figure it out.

 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous

     

    This is one of the way to do this:

     

    1.Create a indx column.

    2. Creata Your new column with this formula: NoDUpCount = IF( CALCULATE(MIN(Table1[Index]), FILTER(Duplicate,Table1[Name]= EARLIER(Table1[Name])  && Table1[Date]= EARLIER(Table1[Date]))) = Table1[Index], Table1[Count],0)

     

    Thanks

    Raj

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous

     

    This is one of the way to do this:

     

    1.Create a indx column.

    2. Creata Your new column with this formula: NoDUpCount = IF( CALCULATE(MIN(Table1[Index]), FILTER(Duplicate,Table1[Name]= EARLIER(Table1[Name])  && Table1[Date]= EARLIER(Table1[Date]))) = Table1[Index], Table1[Count],0)

     

    Thanks

    Raj

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you for the answer. I think it has answered my question for now so I will accept this as my solution but I will keep testing to see if the data turned out how I wanted. Thank you again for helping me out!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Why do you want a calculated column solution.  With a measure, it is a simple solution.

     

    =DISTINCTCOUNT(Data[Count])

    or

    =MIN(Data[Count])

     

    Drag Name and Date to the row labels of your visual.