Определите происхождение и пункт назначения на основе столбца местоположения и даты

Я с трудом обдумываю это. Я пытаюсь построить карту маршрута, и для этого мне нужны исходная точка и пункт назначения. Мои данные выглядят примерно так:

+-----------+-----+-----------+
|   Date    | ID  | Location  |
+-----------+-----+-----------+
| 2/7/2018  | 101 | LA        |
| 2/16/2018 | 101 | Seattle   |
| 2/17/2018 | 101 | San Diego |
| 2/26/2018 | 102 | Arlington |
| 3/20/2018 | 101 | Aberdeen  |
| 5/16/2018 | 102 | Mesquite  |
| 5/17/2018 | 102 | Reisor    |
| 6/12/2018 | 103 | Oxnard    |
+-----------+-----+-----------+

Что я хочу получить в итоге:

+-----------+-----+-----------+-------------+
|   Date    | ID  |  Origin   | Destination |
+-----------+-----+-----------+-------------+
| 2/7/2018  | 101 | LA        | Seattle     |
| 2/16/2018 | 101 | Seattle   | San Diego   |
| 2/17/2018 | 101 | San Diego | Aberdeen    |
| 2/26/2018 | 102 | Arlington | Mesquite    |
| 3/20/2018 | 101 | Aberdeen  | Aberdeen    |
| 5/16/2018 | 102 | Mesquite  | Reisor      |
| 5/17/2018 | 102 | Reisor    | Reisor      |
| 6/12/2018 | 103 | Oxnard    | Oxnard      |
+-----------+-----+-----------+-------------+

Я перепробовал все возможные способы Power Query. Я отсортировал таблицу по дате, идентификатору и местоположению, затем я создал дубликат таблицы и добавил два разных индекса (один от 0, а другой, начиная с 1), а затем я объединил их. Когда я применил его ко всему набору данных, ничего не вышло. Я пробовал поворачивать и откатывать столбцы. У меня нет идей.

Может ли кто-нибудь предложить лучший способ сделать это, чтобы достичь желаемого результата, либо в M, либо в DAX?

Спасибо.


powerbi powerquery dax m
person Cosmin    schedule 11.06.2018    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Чтобы в этом разобраться, запишем правила:

  1. Маршрут обозначается ID.
  2. Origin совпадает с Location.
  3. Для Destination, если это последняя дата на том же маршруте, то Destination = Origin; иначе
  4. Destination будет Location, где это самая ранняя / минимальная дата, которая позже текущей даты на том же маршруте.

Все, что нам нужно сделать, это перевести приведенные выше правила в код (DAX):

Нам нужно знать последнюю дату маршрута (для правила 3). Я назвал его MaxDate:

MaxDate =
CALCULATE(
    MAX(Route[Date]),
    FILTER(
        Route,
        Route[ID] = EARLIER(Route[ID])
    )
)

MaxDate

Нам также необходимо знать следующую дату того же маршрута (Правило 4):

NextDate =
CALCULATE(
    MIN(Route[Date]),
    FILTER(
        Route,
        Route[ID] = EARLIER(Route[ID]) &&
        Route[Date] > EARLIER(Route[Date])
    )
)

NextDate

Теперь нам просто нужно добавить логику для возврата местоположения:

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
        )
    )
)

Конечная цель

person Foxan Ng    schedule 12.06.2018
comment
@Foxan Ng Спасибо за такой невероятный ответ и урок. Я ценю помощь! Это действительно решило мою проблему. - person Cosmin; 13.06.2018
comment
@iCosmin Рад помочь! Только что понял, что MaxDate не является обязательным, и мы можем проверить, является ли NextDate пустым или нет, и достичь того же результата. Я оставлю это на ваше усмотрение :) - person Foxan Ng; 13.06.2018
comment
Еще раз спасибо @Foxan Ng. Я сохраню MaxDate, потому что считаю его полезным на случай, если мне придется проверить в будущем. - person Cosmin; 13.06.2018