Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter data of a table with data from another table

Hi guys, I have the following two tables and a Date table: 

Table1

Person no    Start date    End date   
1    01/05/202015/05/2020
1     01/08/2020    19/05/2020    
2    01/07/2020    01/08/2020   
2     01/12/2020     15/12/2020    
3    01/06/2020     01/08/2020    

 

Table2 contains the dates I want to use as filter for each person. I would like to get only the data with "Start date" after the date that is written in the following table: 

Person NoDate   
1    01/06/2020
2   01/08/2020    
3    01/08/2020   

 

For example: the result should only contain data after 01/06/2020 for person no 1. 

Result table: 

person no    Start date    End Date    
1    01/08/2020   19/05/2020    
2    01/12/2020    15/12/2020    

 

Since only the data after 01/08/2020 is relevant for person no 3, there is no data for that person in the result table.

 

Any ideas how I could get that result? 

Thank you very much!!

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    First create a relationship between the 2 tables based on person no:

    Then create a measure as below:

    Measure = IF(MAX('Table'[Start date])>=MAX('Table (2)'[Date]),1,BLANK())

    Put the measure in the filter pane and choose measure is not blank:

    And you will see:

    For the related .pbix file,pls see attached.

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

2 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    See if this works for you:
    First the model:

    And with this measure:

     

    After date =
VAR DateRef =
    CALCULATE (
        MAX ( 'Table 2'[Date   ] ),
        ALLEXCEPT ( 'Table 2', 'Dim Perons No.'[DPersonNo] )
    )
RETURN
    COUNTROWS (
        SUMMARIZE (
            FILTER ( 'Table 1', 'Table 1'[Start date    ] > DateRef ),
            'Dim Perons No.'[DPersonNo],
            'Table 1'[Start date    ],
            'Table 1'[End date   ]
        )
    )

     

    Select the visual and add the [After date] measure to the filters on the visual and set the value to 1:

     

    I've attached the sample PBIX file

     

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  Anonymous ,

     

    First create a relationship between the 2 tables based on person no:

    Then create a measure as below:

    Measure = IF(MAX('Table'[Start date])>=MAX('Table (2)'[Date]),1,BLANK())

    Put the measure in the filter pane and choose measure is not blank:

    And you will see:

    For the related .pbix file,pls see attached.

     

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!