Level: Intermediate
I was helping a user on http://community.powerbi.com this week. The user had a list of service desk tickets for multiple customers and the requirement was to identify when a customer opened a second new ticket within 7 days of closing a previous ticket. This is not an uncommon scenario so I thought it would make a good blog article.
One of the challenges that new users have when using Power BI is to decide if they should use Power Pivot (DAX modelling) or Power Query (PQL shaping) to solve each problem. And if the answer is DAX, then they also need to decide if it should be a measure or calculated column. There is no one single correct answer to these questions and it always depends on the circumstances. I generally recommend to shape your data properly prior to loading to Power Pivot, that way the DAX formulas are easier to write and the reporting database will be more performant. I looked at the options for this problem and decided to use Power Query to shape the data before loading. Power Pivot and DAX are not really designed to do ordinal row comparisons, so this task is better handled with Power Query.
For this particular problem I thought I would produce a video that demonstrates one way in which this problem can be solved. Here it is.
Learn to be a Power Query Expert
If you want to fast track your learning so you can leverage all the power of Power Query, take a look at my self-paced online Power Query training course. You can also watch some free sample Power Query training videos here to get a feel about how you are going to learn.
This training has over 7 hours of video explaining concepts like this. Once you know how, you will be able to save time and do fantastic things you never dreamed possible as a Power Query expert.
Any way to identify the first record of a repeat call?
I guess you could use a time stamp for the record. You would need to find some logic in the data. If that logic doesn’t exist, then I guess, no.
This was AMAZINGLY helpful and so easy, well after watching your tutorial that is!
Amazing! Thanks for sharing this and solving my headache.
Hi Nancy,
Thanks for bringing this to our notice.
It has been fixed.
Hello – Any way to post the video again?
Hey Matt,
Thanks for posting this! It solved my issue and taught me a few new tricks! Keep up the great content
Would there be a way to count the duplicates per row? For example. lets say customer ABC had 3 instances where the closed date was within the 7 day range, would there be a way to label each duplicate as such:
Cus Name | Duplicate Count
ABC | 1 (original record)
ABC | 2 (within 7 days of original)
ABC | 3 (within 7 days of original)
ABC | 4 (within 7 days of original)
Hi Bans,
You can group your data by Customer, and then Count Rows of the Records for EACH duplicated Close Date label. This will give you the total number of tickets within 7 days.
If instead, you are interested in simply tagging each record with an Index number unique to the customer, you can do this by grouping your data by customer, and then add an index to each grouped table.
Check out the Power Query Academy, to learn how to be a Power Query Ninja!
Video is posted back.
Video seems to be gone. Still relevant in 2021! 🙂
Sorry about that. Thanks for letting me know. It was not intentional. I will see if I can fix it and post back here.
Very brilliant, i am happy that google bring me here!
Thanks for your guide
Hello Matt, thanks for take your time with this explanation, i am using another way to do the same and I’m not sure which of both options are better in performance speaking, the trick I’m using is to add an index column and get the previous row using the previosTable{index-1} of the actual row ex:
PreviousRowAmount= #”PreviousTable”{[Index]-1}[Amount]
if I have a table with a lot of records … do u think is better to use your trick ???
Thanks very much!!!!
I think performance would be the same, but you would have to test it.
Thanks Matt. Above video to compare rows and find difference with Indexes helped me to achieve my requirement.
Thanks man for sharing this video. This helped me a much 🙂
You can’t add an index column in direct query mode, so no, it will not work.
Although it is late but out of curiosity, is it possible using “Direct Query” mode.
Hi Matt,
Here is correct url where I have posted the problem
https://community.powerbi.com/t5/Desktop/fFInd-Difference-Between-Current-and-previous-ticket/m-p/466981#M216868
Dear Matt,
I have posted example in power BI support.
https://community.powerbi.com/t5/Desktop/FInd-Difference-Between-Current-and-previous-ticket/m-p/466976#M216864
It will be highly appreciate if you can help. This is similar issue as you have explain but I have another column Type. So my requirement is to get difference between current row and it’s previous row who’ type is visit, if it doesn’t found match in previous row it will search for next row and search until it not found the match.
https://community.powerbi.com/t5/Desktop/FInd-Difference-Between-Current-and-previous-ticket/m-p/466976#M216864
Sorry, I don’t understand your use case. I suggest you post a sample workbook on a forum (powerpivotforum.com.au or community.powerbi.com) and explain the issues.
Hi,
This is great example but my requirement is to get the difference between current ticket and previous who’s type is visit. In this scenario it will only give the difference between current ticket and previous but not based on filter column. Please help I am working one of the report but not able to find solution.
hello Matt that good example, I would like to know if you can do the same considering more than a previous row, for example that they were more than one previous row. Thank you. regards
How to load DBF files? Any suggestions? As I understand, as of Excel 2016 file extension support was removed.
Shall first convert DBF to XLS in batch, then continue with your wonderful example.
Thanks for this !
I have a similar subject that I can not solve.
You will find the different leave period and i want to know if, for a Id, is there two contiguous period in the list taking into account weekends and holidays
the link for the example : https://docs.google.com/spreadsheets/d/1bHWDhFH196TSav_se8yFO5yOkqz-bjK72YxpdAIsghs/edit?usp=sharing
I assume you can use the same approach I describe in the article. first sort the table appropriately and then fetch the previous end date from the previous record into the current record. From there, just check if there is an overlap between the start date of the current record with the end date of the previous record.
Bravo!
Very helpful – I am certain I will be using this soon. Thank you.
Matt hi, many thanks very useful exercise
This is a very useful and simple solution for such common problems.
Thanks.
Sweet! Great trick and it solve (I think at least) a problem I have been scratching my head about for some time. (“Rouge” record in cvs file that belongs with the one above it in the middle of the record.)