将SQL查询转换为DAX度量值：EARLIER函数相关咨询

Great question! Let's walk through this step by step—first understanding what your original SQL does, then mapping it to equivalent DAX, and finally clarifying how EARLIER() fits into the picture.

First: Recap of Your SQL Logic #

Your SQL query does three key things:

1. Creates a CTE filtering rows where Id_Type = 1 and dRecueil <= 02/04/2018 (I'm assuming this is DD/MM/YYYY, so April 2nd, 2018).
2. Ranks each row within groups of idU_Email + id_Type—prioritizing the most recent dRecueil (descending order) and, for ties, the smallest valeur (ascending order).
3. Counts how many unique idU_Email have their top-ranked row (where rang = 1) with valeur = 0.

Equivalent DAX Measure #

Here's a DAX measure that replicates this logic exactly:

Count_Valid_Emails = 
-- Step 1: Filter down to the same rows as your CTE's WHERE clause
VAR FilteredData = 
    FILTER(
        vUE,
        vUE[Id_Type] = 1 && vUE[dRecueil] <= DATE(2018, 4, 2)
    )
-- Step 2: Add the rank column, matching your SQL's ROW_NUMBER() logic
VAR RankedData = 
    ADDCOLUMNS(
        FilteredData,
        "rang",
        RANKX(
            -- Partition by idU_Email + id_Type (same as your PARTITION BY)
            FILTER(FilteredData, 
                vUE[idU_Email] = EARLIER(vUE[idU_Email]) && vUE[id_Type] = EARLIER(vUE[id_Type])
            ),
            -- Order by dRecueil DESC first, then valeur ASC
            vUE[dRecueil],, DESC,
            vUE[valeur],, ASC
        )
    )
-- Step 3: Filter to only top-ranked rows with valeur = 0
VAR TopRankedValidRows = 
    FILTER(RankedData, [rang] = 1 && [valeur] = 0)
-- Step 4: Count unique idU_Email (matches your final COUNT(idU_Email))
RETURN
    COUNTROWS(DISTINCT(SELECTCOLUMNS(TopRankedValidRows, "idU_Email", [idU_Email])))

How EARLIER() Works in This Measure #

EARLIER() is all about accessing values from an outer row context when you're inside a nested one. Let's break down its role here:

• The ADDCOLUMNS function creates an outer row context: it iterates over every row in FilteredData, one at a time.
• Inside that, we use RANKX, which requires us to define a subset of rows (the partition) to rank against. To get the same partition as your SQL's PARTITION BY idU_Email, id_Type, we need to filter FilteredData to only rows that match the current row's idU_Email and id_Type.
• EARLIER(vUE[idU_Email]) grabs the idU_Email value from the outer row context (the row ADDCOLUMNS is currently processing). Without EARLIER(), DAX would try to use the inner row context's idU_Email (from the FILTER inside RANKX), which would just compare each row to itself—resulting in every row getting a rank of 1 (which is not what we want).

Alternative: Use Variables Instead of EARLIER() #

If you find EARLIER() confusing, you can achieve the same result by capturing the outer context values in variables first. This is often more readable for beginners:

"rang",
VAR CurrentEmail = vUE[idU_Email]
VAR CurrentType = vUE[id_Type]
RETURN
    RANKX(
        FILTER(FilteredData, 
            vUE[idU_Email] = CurrentEmail && vUE[id_Type] = CurrentType
        ),
        vUE[dRecueil],, DESC,
        vUE[valeur],, ASC
    )

This does exactly the same thing as using EARLIER()—we just store the outer context values in variables before using them in the filter.

Is This DAX Equivalent to Your SQL? #

Yes! It matches all the logic in your original query:

• Applies the same initial filters for Id_Type and dRecueil.
• Ranks rows using the exact same partitioning and ordering rules.
• Filters to top-ranked rows with valeur = 0 and counts unique idU_Emails.

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