Forum Discussion

Sharu's avatar
Sharu
Frequent Visitor
7 years ago
Solved

Need help with a measure

Hi all,   I am looking for help with a calculation for a calculated column using the EARLIER() function (at least, that’s what I think). I want to create a backlog of the activities that cannot be ...
Need Help
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    Ah, my apologies.  I hardcoded the minimum capability level (shown in bold) in the previous expression:

     

    BacklogActive = IF('Project Data'[Capability level]=1 || MINX(FILTER('Project Data', 'Project Data'[Project] = EARLIER('Project Data'[Project]) && 'Project Data'[Practice] = EARLIER('Project Data'[Practice]) && 'Project Data'[Capability level]<EARLIER('Project Data'[Capability level])),[Progress])>=1,"Can Start", "Cannot Start")

     In order to change this, we get to make our expression much larger, and may be able to clean it up since we use (mostly) the same filter twice, but for a quick and dirty solution, I'm going to replace the expression as-is.

     

    BacklogActive = IF('Project Data'[Capability level]=MINX(FILTER('Project Data', 'Project Data'[Project] = EARLIER('Project Data'[Project]) && 'Project Data'[Practice] = EARLIER('Project Data'[Practice])), [Capability Level]) || MINX(FILTER('Project Data', 'Project Data'[Project] = EARLIER('Project Data'[Project]) && 'Project Data'[Practice] = EARLIER('Project Data'[Practice]) && 'Project Data'[Capability level]<EARLIER('Project Data'[Capability level])),[Progress])>=1,"Can Start", "Cannot Start")

    And that should do it!

Cmcmahan's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Can you share a set of redacted sample data that shows how your data is set up, and how you expect it to be displayed?  

    • Cmcmahan's avatar
      Cmcmahan
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      So just to confirm I'm reading this data right from the sample data.  You have a [Project] which has a [Practice], which has an [Activity].  You want a check to see if the [Activity] can be started.
      An activity can be started when all activies within the SAME [Practice] but with a LOWER [Capability level] have a progress of 1, indicating they are complete.

      • Cmcmahan's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Here's the calculated column I was able to set up on your sample data to get this result:

         

        BacklogActive = IF('Project Data'[Capability level]=1 || MINX(FILTER('Project Data', 'Project Data'[Project] = EARLIER('Project Data'[Project]) && 'Project Data'[Practice] = EARLIER('Project Data'[Practice]) && 'Project Data'[Capability level]<EARLIER('Project Data'[Capability level])),[Progress])>=1,"Can Start", "Cannot Start")

        This checks if the capability level is 1 to show that level 1 activities can always be started, and then finds the minimum progress of all activities within the same project and practice with a lower capability level. If that minimum is less than 1 (indicating that there is at least 1 activity of a lower capability level that is incomplete) then it spits out "Cannot start".   If the minimum capability level on a project is higher than 1, this will need to be adjusted above

        Here's the resulting matrix from the sample data you gave me: