SQL转DAX咨询：如何用RANKX实现SQL中ROW_NUMBER的分区排序逻辑？

Great question! Let's walk through how to replicate your SQL logic in DAX—since ROW_NUMBER() and RANKX serve similar purposes but require a context-aware approach in DAX.

First, let's recap what your SQL query does:

1. Filters records where Id_Type = 1 and dRecueil is on or before one month ago
2. Partitions the filtered data by idU_Email and id_Type, then assigns a row number ordered by dRecueil DESC (most recent first) and valeur ASC
3. Counts how many unique idU_Email have a row number of 1 and valeur = 1

Here's the equivalent DAX query, with explanations for each step:

VAR FilteredData =
    CALCULATETABLE (
        vUE,
        -- Match SQL's WHERE clause filters
        vUE[Id_Type] = 1,
        vUE[dRecueil] <= DATEADD ( TODAY(), -1, MONTH ) -- Equivalent to SQL's DATEADD(month, -1, GETDATE())
        -- Use EOMONTH(TODAY(), -1) if you need the last day of the previous month instead of exactly one month prior
    )
VAR RankedData =
    ADDCOLUMNS (
        FilteredData,
        "@rang", -- Simulate the SQL ROW_NUMBER() column
        RANKX (
            -- Define the partition: same as SQL's PARTITION BY idU_Email, id_Type
            CALCULATETABLE (
                FilteredData,
                ALLEXCEPT ( FilteredData, vUE[idU_Email], vUE[id_Type] )
            ),
            -- Sort logic: replicate ORDER BY dRecueil DESC, valeur ASC
            -- Convert date to a negative value to reverse sort order, then add a small fraction of valeur to prioritize it second
            -VALUE ( vUE[dRecueil] ) + ( vUE[valeur] / 10000 ),
            , -- Leave the value argument blank to rank the current row's value
            ASC, -- Sort ascending (since we negated the date, this acts as descending for dRecueil)
            SKIP -- Use SKIP to mimic ROW_NUMBER(): unique ranks even for identical values
        )
    )
RETURN
    -- Count records where rank is 1 and valeur = 1
    COUNTROWS (
        FILTER (
            RankedData,
            [@rang] = 1 && vUE[valeur] = 1
        )
    )

Key Notes: #

• Partitioning: ALLEXCEPT(FilteredData, vUE[idU_Email], vUE[id_Type]) tells DAX to keep only the context of idU_Email and id_Type, creating the same partition effect as SQL's PARTITION BY.
• Sorting: Since RANKX defaults to ascending order, we negate the date value to reverse the sort (so newer dates come first). Adding a tiny fraction of valeur ensures that within the same dRecueil, rows are ordered by valeur ascending.
• Rank Behavior: Using SKIP as the last argument in RANKX ensures we get unique row numbers, just like SQL's ROW_NUMBER(). If you used DENSE instead, you'd get duplicate ranks for identical values (matching SQL's RANK() function).

If you need to use this as a measure in a Power BI report, wrap the entire expression in a MEASURE definition:

EmailCountMeasure = 
VAR FilteredData =
    CALCULATETABLE (
        vUE,
        vUE[Id_Type] = 1,
        vUE[dRecueil] <= DATEADD ( TODAY(), -1, MONTH )
    )
VAR RankedData =
    ADDCOLUMNS (
        FilteredData,
        "@rang",
        RANKX (
            CALCULATETABLE ( FilteredData, ALLEXCEPT ( FilteredData, vUE[idU_Email], vUE[id_Type] ) ),
            -VALUE ( vUE[dRecueil] ) + ( vUE[valeur] / 10000 ),
            ,
            ASC,
            SKIP
        )
    )
RETURN
    COUNTROWS ( FILTER ( RankedData, [@rang] = 1 && vUE[valeur] = 1 ) )

内容的提问来源于stack exchange，提问作者KillerDragoonADEN