DAX表达式实现ROW_NUMBER() PARTITION BY ORDER BY的等效功能

Question

我有一个类似于这样的SQL语句：

(ROW_NUMBER() OVER (PARTITION BY a.[market], [MEASURE_TYPE] 
                    ORDER BY AM, REP, ORDER_KEY)) AS ORDER_KEY

我想写一个DAX来实现上述SQL语句。

- Yousuf Sultan

---

您需要使用RANKX函数。 - Andrey Nikolov

你需要将它作为表格中的静态列，还是作为度量值根据筛选器变化实时计算？ - Kosuke Sakai

@KosukeSakai 这应该是一个静态列。 - Yousuf Sultan

一般情况下，应避免使用RANKX函数，因为它是一种非常耗费资源的操作。 - Seymour

Answer 1

在DAX中，这并不像SQL那样简单。以下是一个例子:

Order Key Within Partition = 
VAR CurrentMarket = [Market]
VAR CurrentMeasureType = [MeasureType]
VAR CurrentAM = [AM]
VAR CurrentREP = [REP]
VAR CurrentOrderKey = [OrderKey]

VAR CurrentPartition = FILTER (
    a, -- the table name
    [Market] = CurrentMarket
    && [MeasureType] = CurrentMeasureType
)

RETURN SUMX (
    CurrentPartition,
    IF (
        ISONORAFTER (
            CurrentAM, [AM], ASC,
            CurrentREP, [REP], ASC,
            CurrentOrderKey, [OrderKey], ASC
        ),
        1
    )
)

编辑：使用Power Query功能更好地实现此目的。

let
    /* Steps so far */
    Source = ...,
    ...
    a = ...,
    /* End of steps so far */

    /* Add steps below to add Order Key Within Partition column */
    Partitions = Table.Group(
        a,
        {"Market", "MeasureType"}, {"Partition", each _}
    )[Partition],
    AddedOrderKeys = List.Transform(
        Partitions,
        each Table.AddIndexColumn(
            Table.Sort(_, {"AM", "REP", "OrderKey"}),
            "Order Key Within Partition",
            1
        )
    ),
    Result = Table.Combine(AddedOrderKeys)
in
    Result

- Kosuke Sakai

---

我尝试着实现这个功能，但是在编写新列的代码后，屏幕一直卡在处理上已经四个小时了。我的表格有近三百万条记录。 - Yousuf Sultan

太糟糕了！在这种情况下，基于Power Query的方法会更好。我会更新答案。 - Kosuke Sakai

Power Query的方法非常棒、优雅且快速！非常感谢！向@Kosuke Sakai致敬！ - Simon

Answer 2

我提供了一种替代RANKX的解决方案。包含Power Query的答案是正确的，因为它避免了使用计算列。

Sales[Sequence by Customer] = 

VAR CurrentDate = Sales[Date]

VAR CurrentTime = Sales[Time]

RETURN COUNTROWS (

    FILTER (

        CALCULATETABLE (

            Sales, 

            ALLEXCEPT ( Sales, Sales[Customer] )

        ),

        Sales[Date] < CurrentDate

          || ( Sales[Date] = CurrentDate 

               && Sales[Time] <= CurrentTime )

    )

)

来源

- Seymour