Forum Discussion

IvanS's avatar
IvanS
Icon for Helper V rankHelper V
3 years ago
Solved

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 GENERATESE...
Need Help
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    3 years ago

    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:

    Hopefully, this provides what you are looking for.

    Regards,
    Tom

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
3 years ago

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

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
3 years ago

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:

Hopefully, this provides what you are looking for.

Regards,
Tom

  • IvanS's avatar
    IvanS
    Icon for Helper V rankHelper V
    3 years ago

    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!