基于关联表两列的DAX动态筛选与计算问题

Alright, let's work through this DAX problem together. It sounds like you’ve got the base calculation (B3-C3-D3) sorted, but the tricky part is dynamically pulling in that -B4 + C4 component from the next package—like when calculating Package 10, you need to reference Package 11’s values. Let’s fix up your DAX code to handle that correctly.

Key Issues in Your Current Code #

• Using MAX('Order'[PactDelivered]) + 1 assumes package numbers are perfectly continuous, which might not always hold (even if they are now, this isn’t future-proof for gaps).
• Your NextOrders variable calculates SUM(OrdersSentOut) + SUM(OrdersReturned), but you actually need -OrdersSentOut + OrdersReturned (equivalent to SUM(OrdersReturned) - SUM(OrdersSentOut)).

Corrected DAX Measure #

Cancellations :=
VAR CurrentPack = SELECTEDVALUE('Order'[PactDelivered])
// Get the next existing package number (works even with gaps in PactDelivered)
VAR NextPack =
    MINX(
        FILTER(
            ALL('Order'[PactDelivered]),
            'Order'[PactDelivered] > CurrentPack
        ),
        'Order'[PactDelivered]
    )
// Calculate the next package's contribution: -OrdersSentOut + OrdersReturned
VAR NextPackContribution =
    CALCULATE(
        SUM('Fact DropOff'[OrdersReturned]) - SUM('Fact DropOff'[OrdersSentOut]),
        'Order'[PactDelivered] = NextPack
    )
// Base calculation for the current package
VAR CurrentCalculation =
    SUM('Fact DropOff'[OrdersSentOut])
    - SUM('Fact DropOff'[OrdersReturned])
    - SUM('Fact DropOff'[ActiveSubscriptions])
// Combine both parts; if no next package exists, use 0 instead of BLANK
RETURN
    CurrentCalculation + COALESCE(NextPackContribution, 0)

Breakdown of the Fixes #

1. CurrentPack: Uses SELECTEDVALUE to reliably get the package number from the current row/filter context—this is more precise than MAX when working with individual packages in a table visualization.
2. NextPack: Uses MINX and FILTER to find the smallest package number that’s larger than the current one. This handles gaps in your PactDelivered values (e.g., if Package 10 is followed by 12 instead of 11) instead of blindly adding 1.
3. NextPackContribution: Directly computes the -B4 + C4 logic by subtracting OrdersSentOut from OrdersReturned, which matches your Excel requirement.
4. COALESCE: Ensures that if there’s no next package (like the last one in your list), we add 0 instead of a blank value to keep the calculation clean.

Optional Simplification (If Packages Are Continuous) #

If you’re certain PactDelivered will always be a sequence of consecutive integers, you can replace the NextPack variable with this simpler line:

VAR NextPack = CurrentPack + 1

This will work just fine and is slightly more performant, but the MINX approach is more robust for real-world data where gaps might occur.

Just double-check that your relationship between 'Order' and 'Fact DropOff' is active and correctly set up (you mentioned it’s already established, so this should be good to go!).

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