Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
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!
Solved! Go to Solution.
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)
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)
@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);
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
Hi @amitchandak ,
I have tried to use the measure above with EmployeeEndDate as the calculating field, but I am still running into issues w/ calculation. When I have the slicer for the date period 30-Jun-2018 to 31-Dec-2018, the following data shows (sample below):
EmployeeID | EmployeeEndDate | Earliest (1 - initial column calc) | Earliest (2 - your measure calc) |
A100 | 7-Aug-2018 | 1 | 0 |
A100 | 27-Nov-2018 | 0 | 0 |
B500 | 31-Dec-2018 | 1 | 1 |
C200 | 31-Dec-2018 | 1 | 1 |
Date_EarliestSwitch =
VAR _MAX = MAXX(ALLSELECTED(Table[EmployeeEndDate]),Table[EmployeeEndDate])
VAR Date_EarliestSwitch = MINX(FILTER(ALLSELECTED(Table),Table[EmployeeID] = MAX(Table[EmployeeID]) && Table[EmployeeEndDate] >= _MAX && Table[EmployeeEndDate] <= _MAX),Table[EmployeeEndDate])
RETURN IF (MAX(Table[EmployeeEndDate]) = Date_EarliestSwitch,1,0)
Would you have any advice on this or be able to guide me in the right direction?
Thank you for the help thus far
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |