cancel
Showing results for
Did you mean:
Frequent Visitor

## Nested DAX iteration with multiple conditions

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.

1 ACCEPTED SOLUTION
Super User

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]
)``````
3 REPLIES 3
Super User

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]
)``````
Super User

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
Super User

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``````

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors