Forum Discussion

dwightman2176's avatar
dwightman2176
Frequent Visitor
2 years ago
Solved

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 i...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    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

     

PhilipTreacy's avatar
PhilipTreacy
Super User
2 years ago

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's avatar
dwightman2176
Frequent Visitor
2 years ago

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