Return MAX value into column

[dwightman2176]

Hi. If anyone could help with this I would be most appreciative 🙂

As a bit of background:

• I have a table (Issues) that lists all records that we create for IT related incidents
• For major incidents, we have a parent record (Incident) and potentially multiple child records (Sub-tasks) per parent. There would always be at least 1 child per parent for major incidents
• We have a column called Impact Downtime which lists the numeric downtime figure for each Sub-task, but this is not capture on an Incident row
• We have a column called Issue Type which lists whether the record is an Incident or Subtask
• We have a column called Parent Issue Type which will list Incident on all rowa that relate to Sub-tasks, but will be blank on rows that relate to Incidents (as they don't have a parent)
• We have a column called Parent ID, which lists the Incident ID that is the parent for related sub-task. This field is blank for row that relate to incidents

What I am trying to get to, is a new column called MAX Downtime per Incident, which contains the MAX downtime value  from the different sub-tasks related to each parent incident. Hopefully the following image helps

Thanks in advance

David

[PhilipTreacy]

Hi @dwightman2176 

In your first post you showed columns ID and Parent ID.  I don't see Parent ID in the file.

In your measure you wrote

MAX Downtime Per Incident =

VAR _MaxDT = CALCULATE(MAX('Issues'[Downtime]), FILTER(ALL('Issues'), SELECTEDVALUE('Issues'[ISSUE_KEY]) = 'Issues'[ISSUE_PARENT_ISSUE_KEY]))

RETURN IF(SELECTEDVALUE('Issues'[Issue Type]) = "Incident", _MaxDT)

where you are trying to use the ISSUE_KEY and ISSUE_PARENT_ISSUE_KEY columns but looking at the data, the data isn't structured in the way you used in your example?

Shouldn't the 2nd, 3rd and 4th rows have ISSUE_PARENT_ISSUE_KEY TSO-16868?

Similarly for the other data I've highlighted, they don't follow the same ID->Parent ID pattern you used in your original example.

Phil

[dwightman2176]

Hi @PhilipTreacy. 

Thanks for coming back to me. The image I included in my original post was just done for ease of understanding really. Issue ID is actually ISSUE_KEY and Parent ID is actually ISSUE_PARENT_ISSUE_KEY.

In terms of the structure of the ticket data, then again, my original message was done for ease of documentation and explanation. It does not necessarily follow that entries which follow an incident in the table will be the child sub-tasks for that incident. The reason is that, from a process perspective, we raise a sub-task once an incident has been resolved, so if it runs for several days, then obviously there could be many tickets logged between the original incident and subsequent sub-tasks.

The re-worked table below shows how the data could be structured in the table

I hope that explains matters a little and apologies if I've caused some confusion.

Regards

David

[PhilipTreacy · Accepted solution]

Hi @dwightman2176 

Download PBIX file with the example below

This works for me 

MAX Downtime Per Incident = 

VAR _MaxDT = CALCULATE(MAX('DataTable'[Downtime]), FILTER(ALL('DataTable'), SELECTEDVALUE('DataTable'[Issue ID]) = 'DataTable'[Parent ID]))

RETURN IF(SELECTEDVALUE('DataTable'[Issue Type]) = "Incident", _MaxDT)

In the table visual make sure that Issue ID is set to Show items with no data

Regards

Phil

[dwightman2176]

Hi @PhilipTreacy 

The solution you gave populates the MAX downtime value in a table visual, but is there a way to get the same value populated in a calculated column, so that it returns the MAX value for rows which have incidents, but returns a blank for the rows that relate to sub-tasks?

Thanks
David

[dwightman2176]

Hi @PhilipTreacy ,

Ignore my last message - my table was filtered on a date field that only exists in Sub-tasks, so it wasn't showing any incidents. Once I remove that, the Max value is being displayed.

Thank you so much!

[dwightman2176]

Thanks for coming back to me Phil. Unfortunately I'm just getting blank in every row.

This is the updated query I used:

MAX Downtime Per Incident =

VAR _MaxDT = CALCULATE(MAX('Issues'[Downtime]), FILTER(ALL('Issues'), SELECTEDVALUE('Issues'[ISSUE_KEY]) = 'Issues'[ISSUE_PARENT_ISSUE_KEY]))

RETURN IF(SELECTEDVALUE('Issues'[Issue Type]) = "Incident", _MaxDT)

[PhilipTreacy]

Hi @dwightman2176 

Can you please supply your actual data, or even better your PBIX file,  so I can look at it and write the DAX to work with that.

Regards

Phil

[dwightman2176]

Hi @PhilipTreacy . How do I attach a PBIX file as I get an error saying I cannot attach that file type (same with Excel)

Thanks
David

[PhilipTreacy]

Hi @dwightman2176 

Prob easiest to load the file to somewhere like OneDrive or Google Drive and then link to it from here.

Phil

[dwightman2176]

Hi @PhilipTreacy , hopefully Dropbox is OK?

I've attached a version of the data and PBIX file...hopefully it helps.

Basically we report uptime % in our weekly and monthly reports, which is all based on Sub-task data as that is where we record accurate downtime minutes for each incident.

For a quarterly report that we now produce, we have a to report uptime % based on the number of unique incidents, so I need to take the MAX downtime figure from the one or more sub-tasks that relate to each P1 or P2 incident, and use that in the uptime % calculation. That's why I wanted the MAX Downtime figure in the calculated colunn, so I could just replicate the same calculation we use for the weekly and monthly uptime %.

Thanks

David

https://www.dropbox.com/scl/fi/11yvwhu1m6esk14pcvlwl/Power-BI-Help-Data.pbix?rlkey=hydww4aot308s7r2a...