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 

 

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

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

    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's avatar
      PhilipTreacy
      Super User
      2 years ago

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

        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

  • dwightman2176's avatar
    dwightman2176
    Frequent Visitor
    2 years ago

    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