用于
Destination
来源
将是
我们要做的就是把上面的规则翻译成代码(DAX):
MaxDate
以下内容:
MaxDate =
CALCULATE(
MAX(Route[Date]),
FILTER(
Route,
Route[ID] = EARLIER(Route[ID])
)
)
NextDate =
CALCULATE(
MIN(Route[Date]),
FILTER(
Route,
Route[ID] = EARLIER(Route[ID]) &&
Route[Date] > EARLIER(Route[Date])
)
)
Destination =
IF(
Route[Date] = Route[MaxDate],
Route[Location],
CALCULATE(
LASTNONBLANK(Route[Location], ""),
FILTER(
Route,
Route[ID] = EARLIER(Route[ID]) &&
Route[Date] = EARLIER(Route[NextDate])
)
)
)
VAR
要全部包装并删除中间日期列(上面的步骤只是为了更好地理解):
Destination =
VAR MaxDate =
CALCULATE(
MAX(Route[Date]),
FILTER(
Route,
Route[ID] = EARLIER(Route[ID])
)
)
VAR NextDate =
CALCULATE(
MIN(Route[Date]),
FILTER(
Route,
Route[ID] = EARLIER(Route[ID]) &&
Route[Date] > EARLIER(Route[Date])
)
)
RETURN
IF(
Route[Date] = MaxDate,
Route[Location],
CALCULATE(
LASTNONBLANK(Route[Location], ""),
FILTER(
Route,
Route[ID] = EARLIER(Route[ID]) &&
Route[Date] = NextDate
)
)
)