Need help to assign an index value based on a specific column value in Power BI

[GReyes85]

Hi there,

After nearly two years of not touching Power BI at work, I finally returned to a position where I need to use it. Having said that I would appreciate it if you can help me with the following problem: 

I am given a dataset with the log of activities in a tech support department. The log contains all the activities logged in an incident in a range of dates (last 6 weeks). Each activity contains a unique Incident #, but the incidents can have multiple lines of activities in the file. 

The problem: I need to track and report on each incident that has been elevated from one level of support to another (tier to tier), but I have no idea how to identify (via DAX or Data modeling) the specific line per incident where the tier changes so that I can calculate and visualize the data. Would you be so kind to help me with this, please? 

As you can see in the screenshot there are instances in which the incident gets escalated from one tier to another, please keep in mind that the file contains 20K+ lines. 

Escalation from tier to tier exampleoverall look of the log

Thank you beforehand for the help, if you need me to send more information or files, please let me know. 

Jorge. 

[Anonymous · Accepted solution]

Hi @GReyes85,

Perhaps you can do these in query editor, group raw table category fields and use add index function to add index for the current group:

Create Row Number for Each Group in Power BI using Power Query 

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @GReyes85,

Did these suggestions help for your scenario? If that is the case, you can consider Kudo or accept the suitable one to help other users who faced the same issue to find it more quickly.

If the above not help, you can feel free to post here with some detailed information.

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

Hi @GReyes85,

Perhaps you can do these in query editor, group raw table category fields and use add index function to add index for the current group:

Create Row Number for Each Group in Power BI using Power Query 

Regards,

Xiaoxin Sheng

[lbendlin]

You have the Activity Date column - that can serve as the index  (unless you have unfortunate scenarios where two changes have the same timestamp - hopefully not).  So you can use that column to sort your incidence information and find out the order of escalations/deescalations.

[GReyes85]

Ibendlin, 

Thanks for the suggestion, but that is the case: several incidents may have the exact same time-stamp as the incidents are worked by a large group of tech support analysts

[lbendlin]

In that case you are doomed - unless you have a GUID generator in your ticketing process.

[smpa01]

@GReyes85can you provide a sample data representative of your issue?