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

[ilairdyi]

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

1	10/03/22	20/03/22
2	10/04/22	20/04/22

table2;

ID Name Date

1	John Smith	15/03/22
2	Jane Doe	18/03/22
1	Michael Brown	12/04/22
2	Emily Davis	20/04/22
1	Matthew Taylor	08/03/22
2	Lauren Anderson	14/04/22
1	Ryan Thomas	05/03/22
2	Samantha Thompson	07/04/22
1	Nicholas Hernandez	16/03/22
2	Natalie Moore	19/04/22

I am trying to get this result;

ID Name Date

2	Emily Davis	20/04/22
2	Lauren Anderson	14/04/22
2	Natalie Moore	19/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

[v-xinruzhu-msft · Accepted solution]

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.

[amitchandak]

@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])))