PowerBI中用DAX按Email保留唯一值首条记录的技术问题

Got it, let's get this sorted for you! You want to retain only the first occurrence of each unique Email from your calculated table, and your previous attempts with RANKX/FILTER didn't pan out—let's fix that with a few solid DAX approaches.

Method 1: Using GROUPBY with TOPN (Reliable for All Versions) #

This approach groups your table by Email, then grabs the first row from each group. We use TOPN(1) to ensure we only get the first record, which aligns perfectly with your desired output:

Unique Email First Records = 
GROUPBY(
    YourInputTable, // Replace with your actual calculated table name
    YourInputTable[Email],
    "Firstname", 
    VAR CurrentGroup = CURRENTGROUP()
    // Grab the first row from the group; adjust the sort column if you need a specific "first" order
    RETURN SELECTCOLUMNS(TOPN(1, CurrentGroup, YourInputTable[Firstname]), "Firstname", YourInputTable[Firstname])
)

Method 2: RANKX Done Right (Fixing Your Original Attempt) #

If you want to make RANKX work, the key is to rank rows within each Email group using EARLIER() to reference the outer context. Here's the correct implementation that should resolve your earlier issues:

Unique Email First Records = 
VAR RankedRows = 
    ADDCOLUMNS(
        YourInputTable,
        "GroupRank",
        RANKX(
            // Filter to only rows matching the current row's Email
            FILTER(YourInputTable, YourInputTable[Email] = EARLIER(YourInputTable[Email])),
            YourInputTable[Firstname], // Use this column to define "first" (swap for an index/date if available)
            , ASC, DENSE
        )
    )
// Keep only rows where the rank is 1 (the first entry in each Email group)
RETURN SELECTCOLUMNS(FILTER(RankedRows, [GroupRank] = 1), "Firstname", [Firstname], "Email", [Email])

Method 3: Using INDEX (Simpler for Newer Power BI Versions) #

If you're using Power BI Desktop 2022 or later, the INDEX function makes this task super straightforward—it lets you pick the nth row directly from a filtered set:

Unique Email First Records = 
SELECTCOLUMNS(
    DISTINCT(YourInputTable[Email]),
    "Email", [Email],
    "Firstname",
    VAR CurrentEmail = [Email]
    // Grab the 1st row from the filtered Email group; adjust ORDERBY for custom sorting logic
    RETURN INDEX(1, FILTER(YourInputTable, YourInputTable[Email] = CurrentEmail), ORDERBY(YourInputTable[Firstname]))[Firstname]
)

Quick Notes: #

• Don't forget to replace YourInputTable with the actual name of your calculated table.
• If you have a dedicated index column or date column that defines the "first" record more clearly (instead of relying on Firstname), use that in the sort parameter for more consistent results.

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