Forum Discussion

viralpatel21's avatar
viralpatel21
Helper II
3 years ago
Solved

Identifying Duplicates based on Numbers and name

Hi

 

Hope I do make some sense into my problem. I am currently building a card transaction report. One of the requirements is to identify possible fraudlent transaction by card. 

Query: Identify if a card is being used by more than 1 person.

Within the table there is 2 type of duplicates:

1) Where a single member has made several transction - these are good  (not Fraudlent)

 

2) Where a card has been used in several transaction but by different members (Fraudlent) 

i.e.  In this example card number230783...1794 has been used by 6 members : VI, EL,BR,N,YO and LA

The ideal outcome is i would like to filter on those fraudlent people on a table/ outline them in the report and exclude the ones which are not fraudlent (example 1)

 

Hope this made some sense

Thanks

Viral

Need Help
Show and Tell
Tutorial Requests
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Create a measure along the lines of:

    Duplicates =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Card Holder] ),
    ALLEXCEPT ( 'Table', 'Table'[Card number] )
)

    and use it as a value or as a filter in the filter pane

     

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Create a measure along the lines of:

    Duplicates =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Card Holder] ),
    ALLEXCEPT ( 'Table', 'Table'[Card number] )
)

    and use it as a value or as a filter in the filter pane

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, viralpatel21 

     

    You can try the following methods.
    Sample data:

    Measure:

    Use Count =
VAR _Usecount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Card Holder] ),
        FILTER (
            ALL ( 'Table' ),
            [Card Number] = SELECTEDVALUE ( 'Table'[Card Number] )
        )
    )
RETURN
    IF ( _Usecount = 1, "Not Fraudlent", "Fraudlent" )

    Is this the result you expect?

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.