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

Anonymous 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