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

     

     

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