Forum Discussion

ssbagley's avatar
ssbagley
Icon for Helper III rankHelper III
4 years ago
Solved

How to identify duplicates - same amount and date

I am working with employee expense submissions and want to identify lines for the same date and amount and count the number of "duplicates" identified, by employee.  I'm relatively new to PowerBi and...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    ssbagley looked into it and the measure is working as desired.

     

    You need to put all the axis on Don't Summarize

     

     

    Also, don't use date hierarchy and do the following

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

ssbagley  you can utilize a measure like this

coutOfSubmission =
CALCULATE (
    COUNT ( 'Table'[Amount] ),
    ALLEXCEPT (
        'Table',
        'Table'[Employee ID],
        'Table'[Expense Date],
        'Table'[Amount]
    ),
    FILTER ( VALUES ( 'Table'[Amount] ), 'Table'[Amount] <> 0 )
)

 

It counts the amount by ID,Date,Amount Partition for any non-zero Amount

 

 

  • ssbagley's avatar
    ssbagley
    Icon for Helper III rankHelper III
    4 years ago

    But this only "counts", right? I need to identify lines where the same date and amount appear more than once (duplicate submissions).  For example, if Employee 1 submitted $5 three times on 10/04/2019 - that would flag.  But if Employee 2 only submitted $5 one time on 10/5/2019, that would not flag.  

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

      ssbagley  if you want to do it through measure, you need an index column

      coutOfSubmission = 
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[Amount] ),
        ALLEXCEPT (
            'Table',
            'Table'[Employee ID],
            'Table'[Expense Date],
            'Table'[Amount]
        ),
        FILTER ( VALUES ( 'Table'[Amount] ), 'Table'[Amount] <> 0 )
    )
RETURN
    IF (
        _count > 1,
        "Duplicate Submission Exists",
        "No Duplicate Submission Exists"
    )

       

      The same can be used for calcukated column without requiring an index column

      Please find attached