Nested DAX iteration with multiple conditions

[alaskanko]

I am hopeful you will be able to help me or point me in the right direction to work out a DAX formula to return a calculated table.

I have spend hours trying to figure it out but but have hit a wall and cannot move further.

For the purpose of this illustration, I have a simple table which contains orders. Customer can purchase an order from two different shops. I am trying to work out which are the order numbers of red products which have been purchased before the max expiry date of a yellow product, if sold to the same customer at the same shop.

My table is as follows:

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

The desired output of the calculated table is as follows:

Order ID
       2
      11
      12

As explained, I need to retrieve all the order IDs for the red product purchased by the same customer who has also purchased a yellow product at the same shop as the red product and where the max expiry date of the yellow product is after the order date of the red product.

My table has the following Red products and here is the explanation why they should/shouldn't be included:

• Order ID 1 - Don't flag this as max expiry date of Yellow product (order #4 - 18/12/21) is before the order date of Red product (order #1 - 02/02/22)
• Order ID 2 - Flag this as max expiry date of the Yellow product (order #4 - 18/12/21) is after the order date of Red product (order #2 - 15/06/21)
• Order ID 5 - Don't flag it as the Yellow product (order #6) was sold in a different Shop as Red product (there is no max expiry date of Yellow product in the same shop as the Red product order #5)
• Order ID 11 - Flag this as the max expiry date of Yellow product (order #9) is after the order date of Red product (order #11)
• Order ID 12 - Flag this as the max expiry date of Yellow product (order #9) is after the order date of Red product (order #12)

Hope the above example is clear.

Your advice how to achieve this will be greatly appreciated.

[tamerj1 · Accepted solution]

Hi @alaskanko 
Please refer to attached sample file with the solution

Table 2 = 
SELECTCOLUMNS ( 
    FILTER ( 
        'Table',
        VAR CurrentOfficeCustTable = 
            CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Office], 'Table'[Customer] ) )
        VAR YellowRecords =
            FILTER ( CurrentOfficeCustTable, 'Table'[Product] = "Yellow" )
        VAR LastYellowExpDate =
            MAXX ( YellowRecords, 'Table'[Expiry Date] )
        RETURN
            'Table'[Product] = "Red" && 'Table'[Order Date] < LastYellowExpDate
    ),
    "Red Orders",
    'Table'[Order ID]
)

[BeaBF]

@alaskanko Hi!

Use the following code to calculate a new column:

Acc Date =

VAR MAXDATEYELLOW = CALCULATE(MAX('Table'[Expiry Date]), ALLEXCEPT('Table','Table'[Customer],'Table'[Office]),'Table'[Product] = "Yellow")

VAR COUNTPROD = CALCULATE(DISTINCTCOUNT('Table'[Product]), ALLEXCEPT('Table','Table'[Customer],'Table'[Office]),'Table'[Product] = "Red" || 'Table'[Product] = "Yellow")

RETURN IF('Table'[Order Date] < MAXDATEYELLOW && COUNTPROD = 2 && 'Table'[Product] = "Red", 1, 0)

Returns 1 when all conditions described by you are accepted, so records are to be kept, returns 0 for records to be discarded. 

If that's correct, accept my answer as a solution!

BBF

[johnt75]

Firstly split the customers and shops into separate tables, each linked in a one-to-many relationship with the orders table. This avoids any possible problems with auto exist. Then you can use the below code to generate a table

VAR FirstOrderEver = MIN( 'Orders'[Order Date] )
VAR YellowOrders =
	CALCULATETABLE(
		SUMMARIZE(
			'Orders',
			'Customers'[Customer],
			'Shops'[Office],
			'Orders'[Expiry Date]
		),
		TREATAS( { "Yellow" }, 'Orders'[Product] )
	)
VAR YellowOrdersWithAllDates =
	SELECTCOLUMNS(
		GENERATE(
			YellowOrders,
			DATESBETWEEN(
				'Orders'[Order Date],
				FirstOrderEver,
				'Orders'[Expiry Date]
			)
		),
		"Customer", 'Customers'[Customer],
		"Office", 'Shops'[Office],
		"Order Date", 'Orders'[Order Date]
	)
VAR RedOrders =
	CALCULATETABLE(
		VALUES( 'Orders'[Order ID] ),
		YellowOrdersWithAllDates,
		TREATAS( { "Red" }, 'Orders'[Product] )
	)
RETURN
	RedOrders