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

     

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

    • PhilipTreacy's avatar
      PhilipTreacy
      Super User
      2 years ago

      Hi dwightman2176 

       

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

       

      Phil

      • dwightman2176's avatar
        dwightman2176
        Frequent Visitor
        2 years ago

        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=hydww4aot308s7r2awzhewi6g&st=2vcqpcfp&dl=0