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 ,

 

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!