cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viralpatel21
Helper I
Helper I

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)

viralpatel21_1-1663924965400.png

 

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

viralpatel21_0-1663924907515.png

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

1 ACCEPTED SOLUTION
PaulDBrown
Super User
Super User

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

result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @viralpatel21 

 

You can try the following methods.
Sample data:

vzhangti_0-1664183494940.png

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" )

vzhangti_1-1664183554772.png

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
Super User
Super User

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

result.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.