Forum Discussion

ilairdyi's avatar
ilairdyi
Frequent Visitor
3 years ago
Solved

"Creating a relationship and filtering data by date range in Power BI"

I am trying to establish a relationship between two tables in Power BI and filter the results based on a date range and ID column. Specifically, I want to only see records in the second table where the date falls within the range of the dates in the first table and filter the results to show only ID 2. I am seeking assistance on how to accomplish this task in Power BI.

for example here is table1:

ID date1 date2

110/03/2220/03/22
210/04/2220/04/22


table2;

ID Name Date

1John Smith15/03/22
2Jane Doe18/03/22
1Michael Brown12/04/22
2Emily Davis20/04/22
1Matthew Taylor08/03/22
2Lauren Anderson14/04/22
1Ryan Thomas05/03/22
2Samantha Thompson07/04/22
1Nicholas Hernandez16/03/22
2Natalie Moore19/04/22

 

 

 


I am trying to get this result;

ID Name Date

2Emily Davis20/04/22
2Lauren Anderson14/04/22
2Natalie Moore19/04/22


I am trying to establish a connection between table1 and table2 based on the ID column, so that when I use a slicer to select ID 2, it will display the desired results from table2 that corresponds to the range of dates in table1.

Can this be done?

many thanks


Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ilairdyi 

    You can refer to the following example.

    1.Create a 1:N relationship between two tables.

     

    Then create a new column in table

    Column = IF([Date]>=RELATED('Table'[Date1])&&[Date]<=RELATED('Table'[Date2])&&[ID  ]=2,1,0)

    and filter the column

     

    2.You can create a measure

    Measure = var _filter=FILTER('Table',[ID]=MAX('Table (2)'[ID  ]))
var _date1=MAXX(_filter,[Date1])
var _date2=MAXX(_filter,[Date2])
return MAXX(FILTER('Table (2)',MAX('Table (2)'[Date])>=_date1&&MAX('Table (2)'[Date])<=_date2&&MAX('Table (2)'[ID  ])=2),[Name])

    Output

     

    Best Regards!

    Yolo Zhu

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

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    ilairdyi , Try a measure like 

     

    Sumx(Table1, calculate(Countx(Filter(Table2, Table2[ID] = Max(Table1[ID]) && Table2[Date] >= Max(Table1[date1]) && Table2[Date] <= Max(Table1[date2]) ), Table2[ID])))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ilairdyi 

    You can refer to the following example.

    1.Create a 1:N relationship between two tables.

     

    Then create a new column in table

    Column = IF([Date]>=RELATED('Table'[Date1])&&[Date]<=RELATED('Table'[Date2])&&[ID  ]=2,1,0)

    and filter the column

     

    2.You can create a measure

    Measure = var _filter=FILTER('Table',[ID]=MAX('Table (2)'[ID  ]))
var _date1=MAXX(_filter,[Date1])
var _date2=MAXX(_filter,[Date2])
return MAXX(FILTER('Table (2)',MAX('Table (2)'[Date])>=_date1&&MAX('Table (2)'[Date])<=_date2&&MAX('Table (2)'[ID  ])=2),[Name])

    Output

     

    Best Regards!

    Yolo Zhu

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