Forum Discussion
One filter for two date columns in the same table
Hello,
I have this data model where I have the name of the employee in one table (Let's say table A) his employment start date, and the end date in the same table (Table B, Table A and Table B have a relationship by employee ID) and I have a Date Table. What I need is to be able to filter both dates with one filter/slicer. So I need to filter the start and end dates by a Date Table. Data and e.g. below.
Example data.
The idea of the result I need.
What I want is if I choose to filter the year 2021 I get the result where the start OR end date is in the 2021 year.
I know there is no way to make 2 active relationships between 2 columns in one table and one column in another table. I tried to group a slicer, but I get only dates where the start date AND end date are in the 2021 year, which is not what I need.
So maybe someone knows some kind of workaround with USERELATIONSHIP in CALCULATION or any other workaround, where I would be able to filter the data the way I need?
Thank you.
obuolys123 , On way is independent date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', ('Table'[End Date] >=_min || isblank('Table'[End Date])) && 'Table'[Start Date] <=_max))Also, refer
Or the file attached after signature
1 Reply
obuolys123 , On way is independent date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', ('Table'[End Date] >=_min || isblank('Table'[End Date])) && 'Table'[Start Date] <=_max))Also, refer
Or the file attached after signature