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 can you please send the representative dataset /pbix?

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

I can't - this is confidential data so I cannot share.

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

    This is my code:

    CountOfSubmission3 =
    VAR _COUNT =
    CALCULATE (
    COUNT ( 'Misc Sundry'[Local Amount] ),
    ALLEXCEPT (
    'Misc Sundry',
    'Misc Sundry'[Employee],
    'Misc Sundry'[Expense Date],
    'Misc Sundry'[Local Amount]
    ),
    FILTER ( VALUES ( 'Misc Sundry'[Local Amount] ), 'Misc Sundry'[Local Amount] <> 0 )
    )
    RETURN
    IF (
    _COUNT > 1,
    "DUPLICATE EXISTS",
    "NO DUPLICATE EXISTS"
    )
    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

      ssbagley  this is the same code as mine and you can see that it perfectly works on my dataset. For me to be able to understand, why it does not do the trick in your dataset, I need to take a look at the reprentative datset.

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

        How do I upload/attach a file?