cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twalsh0625 Regular Visitor
Regular Visitor

Finding Repeated values in a table, within a date range

I have a list of cases associated with customers. Basic customer support case info included. I would like a measure in the PBIX table that checks to see if that customer account appears again, with a Case Opened date within X days of the original Case Closed date. A simple BOOLEAN result works from there... What is the easiest way to calculate this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: 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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

9 REPLIES 9
Super User
Super User

Re: Finding Repeated values in a table, within a date range

It depends on your data. please post a sample of the data model/tables of data  so someone can help.  



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
twalsh0625 Regular Visitor
Regular Visitor

Re: Finding Repeated values in a table, within a date range

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....

Highlighted
Super User
Super User

Re: 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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

twalsh0625 Regular Visitor
Regular Visitor

Re: Finding Repeated values in a table, within a date range

I'm combing through this now, will let you know!

twalsh0625 Regular Visitor
Regular Visitor

Re: Finding Repeated values in a table, within a date range

Holy crap did it work!

 

This data is going to be extremely helpful, thank you so much!!!!

mc8297 Frequent Visitor
Frequent Visitor

Re: Finding Repeated values in a table, within a date range

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

 

 

Super User
Super User

Re: Finding Repeated values in a table, within a date range

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?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
mc8297 Frequent Visitor
Frequent Visitor

Re: Finding Repeated values in a table, within a date range

Hi @MattAllington 

That's true and something I hadn't thought about! Smiley Happy  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.

Super User
Super User

Re: Finding Repeated values in a table, within a date range

I always like to come back to the problem and how you want to act on that.  If you just want to visualise the history, then just create a table in Power BI, add the relevant columns and put a filter on the customer.  That way you can see everything on the screen.  If you don't know which customer you want to look at, then how will you know?  Maybe you need a column that counts the number of historical interations, and then use that to determine which customer to investigate.  


My best advice...step back and think through the problem, unencumbered from what you think the solution is.  Once you are clear what the problem is, then rethink the best way to solve that problem with the tools you have.  Or share the problem (as opposed to your preceived solution) with others on the forum for their ideas.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 52 members 1,110 guests
Please welcome our newest community members: