Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Nested DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 
    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]
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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