Forum Discussion
Help with finding duplicate incidents
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.
Hi Anonymous ,
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] ) ) ) > 1sample result:
Please note that one row for customer ID4448 on 3/Jan does not match your criteria of being a duplicate.
Hi Anonymous!
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) 😉
4 Replies
LucaixBolhaResolver I
Hi Anonymous!
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) 😉
- amitchandak
Super User
Anonymous , 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())
- danextian
Hi Anonymous ,
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] ) ) ) > 1sample result:
Please note that one row for customer ID4448 on 3/Jan does not match your criteria of being a duplicate.
