如何构建基于多优先级的订单主表最后追踪数量DAX度量？

Hey Aaron, let's fix up your DAX measure to account for all three priority tiers properly—your initial approach was close, but missed the critical Prio 3 logic and didn't narrow down the priority hierarchy step-by-step. Here's a robust solution that follows your exact rules:

Priority Recap to Confirm #

First, let's align on the priority order we're enforcing:

1. Highest priority: Largest Prio 1 value (since 2-99, bigger numbers = higher priority)
2. Within the top Prio 1 group: Largest Prio 2 value
3. Within the top Prio 1 + Prio 2 group: "OX" takes precedence over "OK" (any other Prio 3 values are ignored unless neither OX nor OK exist)

Final DAX Measure #

Last Tracking Quantity = 
VAR CurrentFilteredOrders = ALLSELECTED('Order Master')
-- Step 1: Isolate the highest Prio 1 in the current context
VAR TopPrio1 = MAXX(CurrentFilteredOrders, 'Order Master'[Prio 1])
VAR OrdersWithTopPrio1 = FILTER(CurrentFilteredOrders, 'Order Master'[Prio 1] = TopPrio1)
-- Step 2: Isolate the highest Prio 2 within the top Prio 1 group
VAR TopPrio2 = MAXX(OrdersWithTopPrio1, 'Order Master'[Prio 2])
VAR OrdersWithTopPrio1And2 = FILTER(OrdersWithTopPrio1, 'Order Master'[Prio 2] = TopPrio2)
-- Step 3: Prioritize Prio 3 (OX > OK) using TOPN to grab the highest-priority row
VAR TopPriorityRow = 
    TOPN(
        1,
        OrdersWithTopPrio1And2,
        -- Assign a priority score: OX = 1 (highest), OK = 2, others = 3
        IF('Order Master'[Prio 3] = "OX", 1, IF('Order Master'[Prio 3] = "OK", 2, 3)),
        ASC -- Sort ascending so lower scores (higher priority) come first
    )
-- Step 4: Return the Quantity from the top-priority row
RETURN
    MAXX(TopPriorityRow, 'Order Master'[Quantity])

Breakdown of Each Step #

Let's walk through why each part matters:

• CurrentFilteredOrders: Ensures we only work with the orders the user has selected (via slicers/filters) instead of the entire table.
• TopPrio1 + OrdersWithTopPrio1: Narrow down to only the rows with the largest Prio 1 value—we don't want to grab a random high Prio 2 from a lower Prio 1 group.
• TopPrio2 + OrdersWithTopPrio1And2: Further narrow down to the rows with the largest Prio 2 within the top Prio 1 group.
• TopPriorityRow: Uses TOPN to select the single highest-priority row based on Prio 3. By assigning a numeric score to OX/OK, we can sort to ensure OX always comes before OK.
• Final MAXX: Pulls the Quantity from that top-priority row. If multiple rows share the absolute highest priority (e.g., multiple OX rows with same Prio1/Prio2), this will return the largest Quantity—adjust to SUM or MIN if needed for your use case.

Why Your Initial Approach Fell Short #

Your original code grabbed the global maximum Prio1 and Prio2 across all orders, which could mix rows from different priority groups (e.g., a row with Prio1=99 and Prio2=50 vs. Prio1=98 and Prio2=99—your code would incorrectly pick the latter's Prio2 max, but the former has a higher Prio1). By narrowing down step-by-step, we ensure we respect the hierarchy properly.

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