Forum Discussion
Advocate IFinding 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?
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/
10 Replies
- MattAllington
Community Champion
It depends on your data. please post a sample of the data model/tables of data so someone can help.
twalsh0625Ok, 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....
- MattAllington
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/
Thanks very much for this post and your helpful blog Matt. It reminds me, I need to continue following your book to learn DAX! All the best
