Identifying Duplicates based on Numbers and name

[viralpatel21]

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

[v-zhangti]

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.

[PaulDBrown · Accepted solution]

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