Forum Discussion
How to filter a Table Visual by earliest date (multiple conditions & slicer)
Hi everyone -
I am trying to generate a fact table (Visual Table) in PowerBI that captures the record with the earliest date than an employee has changed employers AND this date must be within the range defined by a date slicer (e.g. from 01-Jan-2018 to 30-Jun-2018). My current formula goes something like this:
EarliestChangeDate =
var EarliestChangeDate = MINX(FILTER(Table,Table{EmployeeID] = EARLIER(Table[EmployeeID]) && Table[EmployeeEndDate] >= DATE(2018,01,01) && Table[EmployeeEndDate] <= DATE(2018,06,30),Table[EmployeeEndDate])
RETURN IF (Table[EmployeeEndDate] = EarliestChangeDate,1,0)- This formula above currently works but is manual, as I have to change the DATE (YYYY,MM,DD) manually to reflect what is selected in the slicer;
- The date slicer is built from the field 'Table[EmployeeEndDate]';
- The final result I want is an indicator column (1,0) that tells me whether the line item in the Table Visual is the earliest record given the date parameter of 01-Jan-2018 to 30-Jun-2018;
- For example, I want the final table to look something like below for 'A100', where the first row will exist in my source data BUT not in the table visual, and only the second and third row will show with an indicator (1,0)
| EmployeeID | EmployeeEndDate | EarliestChangeDate |
| A100 | 25-Apr-2018 | 1 |
| A100 | 14-May-2018 | 0 |
I've tried many of the online solutions to make the DATE (YYYY,MM,DD) part dynamic but nothing seems to work, so I'm hoping that someone here can help!
Thanks!
Try to create a measure like below:
Measure = VAR EMP_ROW = MAX('Table'[EmployeeID]) VAR MIN_ = CALCULATE(MIN('Table'[EmployeeEndDate]),FILTER(ALLSELECTED('Table'),EMP_ROW='Table'[EmployeeID])) RETURN IF(MAX('Table'[EmployeeEndDate])=MIN_,1,0)
5 Replies
oooxxi123 , You can not use a slicer value in a calculated column. Seems like you are using a calculated column.
You need to create a measure
example
EarliestChangeDate =
var _max = maxx(allselected(Date), Date[Date])
var EarliestChangeDate = MINX(FILTER(allselected(Table),Table{EmployeeID] = max(Table[EmployeeID]) && Table[EmployeeEndDate] >= _max && Table[EmployeeEndDate] <= _max,Table[EmployeeEndDate])RETURN IF (max(Table[EmployeeEndDate]) = EarliestChangeDate,1,0)
Thank you amitchandak for the reply!
I have tried to use the example expression above as a field in my visual table, however it does not load (and when I check the field individually, it does not seem like the expression is working);
- I see that, when defining the '_max' variable, you are using a field called 'Date' - which I'm assuming is from a calendar table of some sorts.
- I currently have a calendar table built but not linked by any relationship to the master table which houses the employee data (end date, ID etc.).
- My slicer is also built using the field 'EmployeeEndDate' rather than the date value from the calendar table
Would this be causing the issue ? Or have I done something wrong here?
Thank you again.
oooxxi123 , Calculate _max, based on slicer date and try
Try to create a measure like below:
Measure = VAR EMP_ROW = MAX('Table'[EmployeeID]) VAR MIN_ = CALCULATE(MIN('Table'[EmployeeEndDate]),FILTER(ALLSELECTED('Table'),EMP_ROW='Table'[EmployeeID])) RETURN IF(MAX('Table'[EmployeeEndDate])=MIN_,1,0)
