DAX expression for ROW_NUMBER() PARTITION BY ORDER BY equivalent

Question

I have a SQL statement like this:

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

I want to write a DAX to implement the above SQL statement.

Stichter

You need RANKX function. – Jog

Do you need this as a static column in a table, or a measure that calculates on the fly in response to the filter changes? – Erde

@KosukeSakai it should be a static column. – Stichter

In general avoid RANKX it is an extremely expensive operations. – Lesterlesya

Answer 1

This is not as simple in DAX as in SQL. Here is an example:

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

EDIT: Power Query would be better to achieve this.

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

Erde

I tried to implement this, but after writing the code for new column, the screen is stuck in working on it for the last four hours. My table has almost 3 million records. – Stichter

Too bad! Power Query based approach would be better in that case. I will update the answer. – Erde

The power query approach is great, elegant and fast! Thank you so much! Kudos to @Kosuke Sakai! – Passade

Answer 2

I contribute with an alternative solution to the RANKX. The answer containing the Power Query is the correct one because avoid using calculated columns.

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 )

    )

)

Source

Lesterlesya