基于多条件嵌套迭代的DAX计算表生成需求求助

需求说明 #

筛选满足以下条件的红色产品Order ID：

• 仅针对红色产品订单
• 同一客户、同一店铺下，该客户在该店铺的黄色产品最大Expiry Date晚于红色产品的Order Date

原始订单表 #

Order ID    Office  Customer    Order Date  Expiry Date Product
       1    Shop1   Cust1       02/02/2022  27/08/2022  Red
       2    Shop1   Cust1       15/06/2021  04/02/2022  Red
       3    Shop1   Cust1       30/09/2022  29/04/2023  Blue
       4    Shop1   Cust1       07/05/2021  18/12/2021  Yellow
       5    Shop1   Cust2       30/05/2021  23/05/2022  Red
       6    Shop2   Cust2       08/02/2022  13/01/2023  Yellow
       7    Shop1   Cust2       03/09/2022  13/04/2023  Blue
       8    Shop1   Cust3       24/04/2021  11/07/2021  Yellow
       9    Shop1   Cust3       23/02/2022  21/01/2023  Yellow
      10    Shop1   Cust3       03/06/2022  24/11/2022  Blue
      11    Shop1   Cust3       04/09/2021  28/08/2022  Red
      12    Shop1   Cust3       05/09/2021  28/08/2022  Red

期望输出计算表 #

Order ID
       2
      11
      12

筛选规则说明 #

• Order ID 1：不纳入，该客户同店铺黄色产品最大Expiry Date（Order #4 - 18/12/21）早于红色产品Order Date（02/02/22）
• Order ID 2：纳入，该客户同店铺黄色产品最大Expiry Date（Order #4 - 18/12/21）晚于红色产品Order Date（15/06/21）
• Order ID 5：不纳入，该客户黄色产品（Order #6）与红色产品不在同一店铺，无同店铺黄色产品最大Expiry Date
• Order ID 11：纳入，该客户同店铺黄色产品最大Expiry Date（Order #9）晚于红色产品Order Date
• Order ID 12：纳入，该客户同店铺黄色产品最大Expiry Date（Order #9）晚于红色产品Order Date

DAX实现公式 #

完整计算表公式 #

QualifiedRedOrders = 
VAR RedOrders = FILTER('订单表', '订单表'[Product] = "Red")
VAR CustomerShopYellowMax = SUMMARIZE(
    FILTER('订单表', '订单表'[Product] = "Yellow"),
    '订单表'[Customer],
    '订单表'[Office],
    "MaxYellowExpiry", MAX('订单表'[Expiry Date])
)
RETURN
SELECTCOLUMNS(
    FILTER(
        NATURALLEFTOUTERJOIN(RedOrders, CustomerShopYellowMax),
        NOT ISBLANK([MaxYellowExpiry]) && [MaxYellowExpiry] > [Order Date]
    ),
    "Order ID", '订单表'[Order ID]
)

公式拆解说明 #

1. RedOrders：先筛选出所有红色产品的订单记录
2. CustomerShopYellowMax：通过SUMMARIZE聚合每个客户+店铺组合下，黄色产品的最大Expiry Date
3. NATURALLEFTOUTERJOIN：将红色订单与聚合表关联，自动匹配相同客户+店铺的记录
4. 最终筛选：排除无同店铺黄色产品的记录，同时确保黄色产品最大过期日期晚于红色订单的下单日期，最后仅保留Order ID列

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