Forum Discussion
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 started yet, because the activities with a lower capability level within the same project and parent are not completed yet. I came up with the following formula hoping that it would work, but unfortunately that is not the case, as it cannot find the fields that are written within the brackets of earlier():
backlog activiteiten = IF(Table1[Project] = Table1[Project] && Table1[Practice] = Table1[Practice] && Table1[Progress] <> "Voltooid" && Table1[Activity] > EARLIER('Table1'[Activity]) && Table1[Capability level] > EARLIER('Table 1'[Capability level]), "You cannot start with this activity yet.", "You can start with this activity.")
An example of an activity: APO01.1.1, APO01.1.2.
An example of a capability level: 3.
Thanks in advance!
Kind regards,
Sharu
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!
7 Replies
- Cmcmahan
Resident Rockstar
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?
Hi,
Cmcmahan Yeah sure!
This is what I would like to achieve:
The link to the sample data: https://drive.google.com/file/d/1KBX68KQ85zV4RBL7FjIwirnzV8VZRKU3/view?usp=sharing
Thanks!
Kind regards,
Sharu
- Cmcmahan
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.
