Forum Discussion
Finding Repeated values in a table, within a date range
This is quite a tricky problem. I think it is best solved before you load the data rather than trying to solve it in Power BI using DAX.
I have a working solution for your here. https://www.dropbox.com/s/51eicg5p2wo6sfu/repeat%20ticket.pbix?dl=1
I had to use a stack of Power Query tricks to so I could find the previous record for each cusotmer and compare the close date. I think this needs a blog article to explain as there a lot in it. Let me see what I can do for this Tuesday.
Edit: this is now on my blog at http://exceleratorbi.com.au/use-power-query-compare-database-records/
Ok, bear with me:
Case_ID Customer Open Date Close Date
1234 ABC 5/1/17 5/2/17
4567 XYZ 5/9/17 5/12/17
8888 ABC 5/3/17 5/5/17
I would like to flag CASE_ID as a repeat issue because it is on Customer ABC with an open date that is within 7 days of the close date of another ABC case....
This is quite a tricky problem. I think it is best solved before you load the data rather than trying to solve it in Power BI using DAX.
I have a working solution for your here. https://www.dropbox.com/s/51eicg5p2wo6sfu/repeat%20ticket.pbix?dl=1
I had to use a stack of Power Query tricks to so I could find the previous record for each cusotmer and compare the close date. I think this needs a blog article to explain as there a lot in it. Let me see what I can do for this Tuesday.
Edit: this is now on my blog at http://exceleratorbi.com.au/use-power-query-compare-database-records/
Holy crap did it work!
This data is going to be extremely helpful, thank you so much!!!!
I'm combing through this now, will let you know!
Hello MattAllington
I'm so glad I found this. It works!!! I used this same logic on my data and it's amazing.
I'm curious though - This seems to compare 1 Row to Previous 1 Row. Is it possible to compare 1 Row to all previous rows with the same Customer ID? For example after sorting with Customer ABC -it compares case ID 1234 to 8888. But if i Added another Case ID - lets say (9999) to Customer ABC how can I tell the logic to compare Case ID 9999 to Case 1234 as well as Case 8888?
Case ID 9999, CustomerID: ABC Open Date:5/6/17 Closed Date: 5/6/17
Yes, my solution compares each record for a customer with the previous record. I am not sure what else you could do. if you had 10 records, which ones would you want to compare. In theory there would be 10! (10 factorial, or 3.6 million) comparisons that were possbile. How would you visualise that, let alone act on that?
That's true and something I hadn't thought about! :) You explained it perfectly. My thought was along the lines of building in an 'trail' of repeats. So I thought maybe if it could compare to each row it could provide that.
So I could add a custom column that says for "Repeat Case ID Pathway" and for Customer: ABC, Case ID 8888, 9999 would be in that cell.