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]
)
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
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]
)