Forum Discussion
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 t...
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.
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.