Forum Discussion
Advocate IFinding 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/
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.
Community ChampionI 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.