Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a MS Project with Site, Lot#, and Task Name. Each task has a start Finish Date and % complete. The Task Outline Number corresponds by site, Lot# and Task. For example:
Site | Lot# | Task Name | Start Date | Finish Date | Task Outline Number |
Chicago | C1 | Initiate | 1/1/2021 | 1/1/2021 | 1.1.1 |
Chicago | C1 | Release | 1/1/2021 | 1/1/2021 | 1.1.2 |
Chicago | C1 | Report | 1/1/2021 | (release date + 30 eDays) | 1.1.3 |
Chicago | C4 | Initiate | 3/1/2021 | 3/1/2021 | 1.2.1 |
Chicago | C4 | Release | 3/1/2021 | 3/1/2021 | 1.2.2 |
Chicago | C4 | Report | 3/1/2021 | (release date + 30 eDays) | 1.2.3 |
Indiana | T2 | Initiate | 5/1/2021 | 5/1/2021 | 2.1.1 |
Indiana | T2 | Release | 5/1/2021 | 5/1/2021 | 2.2.1 |
Indiana | T2 | Report | 5/1/2021 | (release date + 30 eDays) | 2.2.3 |
How would I go about writing a Dax expression that "links" together the Report - Finish Date from the report to the Release - start date, by "Group" ie Task Number. So if I wanted to know if the Report was finished for Chicago Lot C1, I would look at the Task Outline Number (1.1) find the Release date (1.1.2) and then group it with the corresponding Report (1.1.3) to check the finish date?
I know it sounds confusing but there has to be a way to form "groups", right? Thanks.
Solved! Go to Solution.
Like this?
Measure2 =
VAR _sel = SELECTEDVALUE('Table'[Task Outline Number])
VAR _NR = LEFT(_sel,3)
RETURN
CALCULATE(MIN('Table'[Finish Date].[Date]), FILTER(ALLSELECTED('Table'), [Task Name ] = "Release" && LEFT([Task Outline Number],3) = _NR))
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Then this should be it?
Measure2 =
VAR _sel = SELECTEDVALUE('Table'[Task Outline Number])
VAR _NR = LEFT(_sel,3)
RETURN
CALCULATE(MIN('Table'[Finish Date].[Date]), FILTER(ALLSELECTED('Table'), CONTAINSSTRING([Task Name ],"Release") && LEFT([Task Outline Number],3) = _NR))
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Like this?
Measure2 =
VAR _sel = SELECTEDVALUE('Table'[Task Outline Number])
VAR _NR = LEFT(_sel,3)
RETURN
CALCULATE(MIN('Table'[Finish Date].[Date]), FILTER(ALLSELECTED('Table'), [Task Name ] = "Release" && LEFT([Task Outline Number],3) = _NR))
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Steve. thanks so much for this helpful information. Can you kindly tell me how I would change the filter portion the code so that it does not have to = "release" but contains in the TASKNAME field the word release.
Right now it has to = (equal) "release"
Thanks so much,
Hi,
Welcome,
Not sure what you mean?
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
HI Steve.
Currently in the Return we have a FILTER on the taskName so that it returns only those Task with the Name that equals (exactly) the word release. However, the task name(s) actually "contains" the word "release".
So it would look something like this (but I cannot get the CONTAIN function to work). Does this help explain it?
RETURN
CALCULATE (MIN('Table'[Finish Date].[Date]), CONTAINS(ALLSELECTED('Table'), [Task Name] "Release"
Thanks -
BTW I have been responding but not all my responses get posted. I really appreciate your help. I am so close!
Then this should be it?
Measure2 =
VAR _sel = SELECTEDVALUE('Table'[Task Outline Number])
VAR _NR = LEFT(_sel,3)
RETURN
CALCULATE(MIN('Table'[Finish Date].[Date]), FILTER(ALLSELECTED('Table'), CONTAINSSTRING([Task Name ],"Release") && LEFT([Task Outline Number],3) = _NR))
File is attached.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Thank you so much, this IS GREAT! I could not figure out how to add the CONTAINSSTRING.
MUCH APPRECIATED
@Pedagogic368 wrote:
Thank you so much, this IS GREAT! I could not figure out how to add the CONTAINSSTRING.
MUCH APPRECIATED
Welcome, glad to be of help.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi Steve, thank you very much for your reply and your attached file. I have a few questions (almost there!). Here is your example with my data.
Update on the table information given above.
The task ID is unique to each summary and there are Task Outline levels associated with each task (ie Task Outline Level - Parent summary task = Level 3 and child summary task (Initiate) = Level 4 . The report = Level 5)
Example for Chicago I want to link the Release Report (Level 5) Date to the Initiate Date on Level 4 :
Chicago. - The Parent Summary Task (Level 3) Task Outline Number = 1.1.1
Chicago - Initiate. Child Summary Task (Level 4) = 1.1.1.2
Chicago - Report. (Level 5) Task Outline Number 1.1.1.2.4
Would I first form a Group of all the task under 1.1.1.X (write a DAX that says group first four digits). Extract the Finish Date associated with Initiate and then add +30edays to that date to calculate the Finish date the Report should be completed? Thoughts
So far no help. So let me ask one more time for some advice. 😔
Here is where I am at.
Each "grouping" has a similar TaskOutline number. So as I mentioned previously -
Group A
Chicago is 1.1.1
Chicago- Initiation is 1.1.1.2
Chicago-Report is 1.1.1.4.2
I truncated all to a value of 1.1.1
Group B
Indiana is 2.1.1
Indiana - Initiation is 2.1.1.2
Indiana -Report is 2.1.1.4.2
I truncated all to a value of 2.1.1
So, as you can see I have several differnt "groups" what I need to do is write a DAX expression that search within each group the Finish Date for the XXXXX-Initiate in each group and then the Start Date for the XXXXX-Report in the same group. HOW DO I DO THIS? THANKS in advance for your help. Please! 😶
So I wrote this out -
I took the Chicago - Report and I wrote a simple Dax expression that pulls out the ParentTaskName (in the example above the ParentTask Name is Chicago-Initiate. I called this Measure1. (FYI - The ParentTaskName is a column that exist on the table). Simple!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |