cross checking between 2 tables

[akfir]

i have 2 tables:
1. Operation X - which presents all dates and customers who did a specific operation - a simple customers dates dimension table (customer can be presented more than once with different dates)
2. Messages Delivered - which presents all messages delivered to all customers.

i wish to check for the customers in table1 for the range of 2 days prior to the date of its operation whether there is a message delivered in that range and return its message ID & channel

sample is attached.

thanks,
Amit

[johnt75]

You could generate a calculated table like

Messages table =
GENERATE (
    'Operation X',
    VAR ReferenceCustomer = 'Operation X'[Customer ID]
    VAR ReferenceDate = 'Operation X'[Date]
    RETURN
        CALCULATETABLE (
            SELECTCOLUMNS (
                'Messages Delivered',
                'Messages Delivered'[Message ID],
                'Messages Delivered'[Date]
            ),
            'Messages Delivered' >= ReferenceDate - 2
                && 'Messages Delivered'[Date] <= ReferenceDate,
            'Messages Delivered'[Customer ID] = ReferenceCustomer
        )
)

[akfir]

Thanks for your response!
i tried your solution but it deleted lots of rows from the main "Operation X" table. i need to have all rows exactly from this table adding just the 2 columns i mentioned. i guess it only returned the matched ones with "Messages Delivered".
one more thing - if there are more than 1 message delivered in that 2 days range , it should always take the latest message.

thanks!

[johnt75 · Accepted solution]

OK, try

Messages table =
GENERATEALL (
    'Operation X',
    VAR ReferenceCustomer = 'Operation X'[Customer ID]
    VAR ReferenceDate = 'Operation X'[Date]
    VAR SummaryTable =
        CALCULATETABLE (
            TOPN (
                1,
                'Messages Delivered',
                'Messages Delivered'[Date], DESC,
                'Messages Delivered'[Message ID], DESC
            ),
            'Messages Delivered' >= ReferenceDate - 2
                && 'Messages Delivered'[Date] <= ReferenceDate,
            'Messages Delivered'[Customer ID] = ReferenceCustomer
        )
    RETURN
        SELECTCOLUMNS (
            SummaryTable,
            'Messages Delivered'[Message ID],
            'Messages Delivered'[Date]
        )
)

this will return a row from Operation X even if there were no messages

[akfir]

i am looking to challenge this a little bit and JOIN to the "Messages Delivered" other 2 tables - assume in the same format - and only THEN running the cross-checking.
hope its clear. any idea? 

[akfir]

Perfect! thanks!