Help with finding duplicate incidents

[RayMe]

Hi there

Hope someone out there can help with this problem 🙂 

I am looking for a way to add a condition that shows duplicate Incidents for a customer on the same day.

In general, the data is similar to what is in the table below.

Date	CostomerID	IncidentID	Incident	Issue here
01/01/2022	ID4444	IA-01	IncidentA	<
01/01/2022	ID4444	IA-02	IncidentA	<
01/01/2022	ID4444	IA-03	IncidentB
02/01/2022	ID4445	IA-04	IncidentA
02/01/2022	ID4445	IA-05	IncidentB
03/01/2022	ID4448	IA-06	IncidentA	<
03/01/2022	ID4449	IA-07	IncidentA	<
03/01/2022	ID4449	IA-08	IncidentA	<
03/01/2022	ID4449	IA-09	IncidentB

There are multiple incidents per day, but they are never same.

I would like to see the date, customer, and incident information that has an issue.

I hope this makes sense.

[danextian · Accepted solution]

Hi @RayMe ,

Try this  as a calculated column:

Incident Count > 1 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[CostomerID] = EARLIER ( 'Table'[CostomerID] )
            && 'Table'[Date] = EARLIER ( 'Table'[Date] )
            && 'Table'[Incident] = EARLIER ( 'Table'[Incident] )
    )
) > 1
 

sample result:

Please note that one row for customer ID4448 on 3/Jan does not match your criteria of being a duplicate.

[LucaixBolha · Accepted solution]

Hi @RayMe!

As far I've understood, your main criteria is to see all the information that also has an issue with it. If so, I'm thinking about a switch, where you could select "Has an issue", and filter only the ones that have it. I would do it this way:

1) Create a new table on power query, containing these info: 

2) After you create a measure for the value of the switch:

3) You create a measure for the information to be shown:
_If issue = if(

    'your table name'[column issue] = "<",

    'your table name'[column issue],

    BLANK() )

4) You create a measure for the switch itself:

5) You put in a slicer the first column "Has an Issue" of the first table that you've created (in step 1), in order to filter your data, and show only the rows that have an issue:

I think it should work 😄 

*If it works, please remember to mark me as a solution (and to give a kudo wouldn't be bad also hahaha) 😉

[danextian · Accepted solution]

Hi @RayMe ,

Try this  as a calculated column:

Incident Count > 1 =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[CostomerID] = EARLIER ( 'Table'[CostomerID] )
            && 'Table'[Date] = EARLIER ( 'Table'[Date] )
            && 'Table'[Incident] = EARLIER ( 'Table'[Incident] )
    )
) > 1
 

sample result:

Please note that one row for customer ID4448 on 3/Jan does not match your criteria of being a duplicate.

[RayMe]

Omg, it did exactly what I needed, thank you for the help

[amitchandak]

@RayMe , You can create a flag like

new column =

var _cnt = countx(filter(Table, [CostomerID] = earlier([CostomerID] && [date] =earlier([Date]) && [IncidentID] =earlier([IncidentID]) ) ,[IncidentID])

return

if(_cnt>1, 1, blank())