Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
IvanS
Helper V
Helper V

Find all related projects for task based on date criteria

Hi guys,

 

I am trying to figure out how can find and add all active projects in time of task creation based on project start date and project end date. I was trying to use CROSSJOIN and GENERATESERIES but unfortunately I have only managed to calculate number of active project in the time of task creation.

 

 

Active Projects = 
VAR _ActiveProjectCount = 
COUNTROWS(
    FILTER(
    DIM_Projects, 
    NOT ISBLANK(DIM_Projects[START_DATE]) &&
    'FACT_Tasks'[START_DATE] >= DIM_Projects[START_DATE] && 
    OR('FACT_Tasks'[START_DATE] < DIM_Projects[END_DATE], ISBLANK(DIM_Projects[END_DATE]))
))

RETURN
IF(ISBLANK(_ActiveProjectCount), 0, _ActiveProjectCount)

 

 

Please see example below:

I have 2 tables: FACT_Tasks and DIM_Projects. There is no relationship between these tables.

 

FACT_Tasks

TASK_IDSTART_DATE
117.11.2022
215.12.2022
320.1.2023

 

DIM_Projects

PROJECT_NAMESTART_DATEEND_DATE
ABC20.11.202219.12.2022
DEF14.12.2022 
XYZ15.1.2023 

 

Now, I would need to list all projects in "Active projects" field in FACT_Tasks table separated by comma. It does not matter if it is calculated as column/measure or new calculated table.

 

TASK_IDSTART_DATEActive projects (desired field)
117.11.2022(blank)
215.12.2022ABC,DEF
320.1.2023DEF,XYZ

 

Any help is much appreciated!


Thank you

IvanS

 

1 ACCEPTED SOLUTION

Hey @IvanS ,

 

based on the business rule I described in my previous post, the DAX statement below creates a calculated column inside the table Fact_Tasks:

 

Active Projects = 
 var TaskStartDate = 'Fact_Tasks'[START_DATE]
 var activeProjects = 
    FILTER(
        'Dim_Project'
        , 'Dim_Project'[START_DATE] <= TaskStartDate && (ISBLANK('Dim_Project'[END_DATE] ) || 'Dim_Project'[END_DATE] >= TaskStartDate )
    )
 return
CONCATENATEX(
    activeProjects
    , 'Dim_Project'[PROJECT_NAME]
    , ","
    , 'Dim_Project'[PROJECT_NAME] , ASC
)

 

The next picture shows the result:
image.png
Hopefully, this provides what you are looking for.

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Write this calculated column formula in the Task table

Column = CALCULATE(CONCATENATEX(VALUES(Projects[PROJECT_NAME]),Projects[PROJECT_NAME],","),FILTER(Projects,Projects[START_DATE]<=EARLIER(Tasks[START_DATE])&&Projects[End date]>=EARLIER(Tasks[START_DATE])))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
TomMartens
Super User
Super User

Hey @IvanS ,

 

can you please explain why Task ID 1 has no active projects and why project XYZ is  not active in Task 2.
I assume Project ABC is not active in task 3, because the project closed before the task started.

Please list all relevant business rules.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens ,

 

I am looking for the nr. of active project in time of task creation.

Task 1 was created before all the projects. This will most likely not happen in real-life but wanted to highlight.

Task 2 was created before project XYZ, therefore this project was not active in time of Task 2 creation.

And regarding Task 3, your assumption is correct 🙂

 

 

Hey @IvanS ,

 

hmm, I'm still not sure if the business rules are fully described by your answer. For this reason I give it a try:

Projects are consider active for a task, when the

  • task starts on or after the project start date
  • but before the project end date.

Regarding the object to create CALCULATED COLUMN or MEASURE, is the expected result affected by user interaction with slicers.

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey @IvanS ,

 

based on the business rule I described in my previous post, the DAX statement below creates a calculated column inside the table Fact_Tasks:

 

Active Projects = 
 var TaskStartDate = 'Fact_Tasks'[START_DATE]
 var activeProjects = 
    FILTER(
        'Dim_Project'
        , 'Dim_Project'[START_DATE] <= TaskStartDate && (ISBLANK('Dim_Project'[END_DATE] ) || 'Dim_Project'[END_DATE] >= TaskStartDate )
    )
 return
CONCATENATEX(
    activeProjects
    , 'Dim_Project'[PROJECT_NAME]
    , ","
    , 'Dim_Project'[PROJECT_NAME] , ASC
)

 

The next picture shows the result:
image.png
Hopefully, this provides what you are looking for.

Regards,
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

This is just perfect! Thank you @TomMartens 

For solution provided by @Ashish_Mathur , this solution worked as well but Tom's solution is also sorting the projects 🙂  Thank you as well!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.