Forum Discussion
Return MAX value into column
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
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
10 Replies
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
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!
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)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
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
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
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
