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        ...
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)

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.