cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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.")

 

12.png

An example of an activity: APO01.1.1, APO01.1.2.
An example of a capability level: 3.

 

Thanks in advance!

 

Kind regards,

Sharu

1 ACCEPTED SOLUTION
Resident Rockstar
Resident Rockstar

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!

View solution in original post

7 REPLIES 7
Resident Rockstar
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:

2.png

The link to the sample data: https://drive.google.com/file/d/1KBX68KQ85zV4RBL7FjIwirnzV8VZRKU3/view?usp=sharing

 

Thanks!

 

Kind regards,

Sharu

Resident Rockstar
Resident Rockstar

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.

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:

 

 


snipo.PNG

Hi, @Cmcmahan,

 

Thanks for your help! The calculation does work, but not fully as I had hoped.

 

As you have already explained, if the minimum capability level within a project (and practice) is higher than 1 (e.g. the activity in Project Z and see the picture below), then it needs to be adjusted in the calculation. I would like to display the backlog at once in one table and by adjusting the calculation, the expected result is not shown for the other projects/practices. Is there any way to overcome this? I have tried to figure this out myself, but no luck so far..

 

2.PNG

 

Thanks in advance!

 

Kind regards,

Sharu

Resident Rockstar
Resident Rockstar

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!

View solution in original post

Hi @Cmcmahan ,

 

It works like a charm! Thank you!

 

Kind regards,

Sharu

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors