Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter Rows based on dates in 2 tables

Hey all!

 

Firstly, a sample of my 2 tables.

 

Table1: Project                                                                       

CustomerID    |   ProjectID     | ProjectDateCreated                 

1                             5                     2020-13-05                               

2                             5                     2020-13-05                                

3                             5                     2021-01-01                                

 

 Table2: Activities     

CustomerID    |   ActivityType     | ActivityDate                 

1                            Call                     2020-21-05                               

1                           Visit                     2020-05-05                                

2                            Mail                    2020-03-01   

2                            Call                     2021-10-01

3                            Visit                    2020-08-10

 

What I want to do is to: Check all the activities that have taken place, after a Project was planned on that customer. So in this example activities in red should be filtered, because the ActivityDate < ProjectDateCreated on CustomerID. It doesn't matter if the Project isn't related to the Activity. 

I've tried the following calculated column, to filter out the activities:

Relevance= IF(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] && Activities[ActivityDate] >= Project[ProjectDateCreated]), 1,0)
 
But this give me an error saying the expression is trying to refer to multiple columns.
Sidenote: The dates column is actually just a string of numbers "20210501", but for clarity sakes, I've shown it differently in my example
 
1) Could I filter these rows in the Power Query editor using M? If so, I'd love to hear it!
2) How do I need to change my DAX in order for it to work, in case point 1 isn't possible.
 
I'd love to hear it and thanks in advance,
Daniël 🙂
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , create a new column like

     

    Relevance=
    var _1 = Countx(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] && Activities[ActivityDate] >= Project[ProjectDateCreated]),Projects[CustomerID])
    return
    if(isblank(_1),0,1)

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , create a new column like

     

    Relevance=
    var _1 = Countx(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] && Activities[ActivityDate] >= Project[ProjectDateCreated]),Projects[CustomerID])
    return
    if(isblank(_1),0,1)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Maybe yet another question regarding your answer.

      Let's say the Activity can be longer than 30 days away from ProjectDateCreated and should also look 7 days in the past. How would I add that logic?

      I've tested "+30" on numerous spots, but I am not sure.

       

      So activities 7 days in the past, prior to the creation of the event and no more than 30 days after the creation of the event. I'd love to hear it :)!

       

      Would je be willing to tell me?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Try this measure

    Relevance = 
IF (
    CALCULATE (
        MAX ( 'Activities'[ActivityDate] ),
        ALLEXCEPT ( Activities, Activities[CustomerID] )
    )
        < CALCULATE (
            MAX ( 'Project'[ProjectDateCreated] ),
            ALLEXCEPT ( 'Project', Project[CustomerID] )
        ),
    1,
    0
)

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.