代码之家  ›  专栏  ›  技术社区  ›  Cosmin

根据位置栏和日期确定出发地和目的地

  •  0
  • Cosmin  · 技术社区  · 7 年前

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

    谢谢。

    1 回复  |  直到 7 年前
        1
  •  3
  •   Foxan Ng    7 年前
  • 路由由 id>表示。
  • destination will be the location where it is the earst/minimum date that is later than the current date on the same route.

  • 我们需要知道路线的最后日期(规则3)。在这里,我把它命名为 maxdate :。

    计算( 过滤器( route[id]=更早(route[id]) )
    nextdate=
    最小值(路线[日期]),
    路线,
    )
    
    
    
    
    目的地=
    如果(
    计算(
    过滤器(
    route[id]=更早(route[id])&&
    )
    
    
    

    实际上,您可以使用var将其全部包装并删除临时日期列(上面的步骤只是为了更好地理解):。

    变量最大日期= ) 计算( 过滤器( 路由[日期]>更早(路由[日期]) ) 路线[位置], 路线, ) SLocation
  • 用于 Destination 来源
  • 将是

  • 我们要做的就是把上面的规则翻译成代码(DAX):

    MaxDate 以下内容:

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

    MaxDate

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

    Destination

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

    Final Destination