Forum Discussion

akfir's avatar
akfir
Helper V
3 years ago
Solved

cross checking between 2 tables

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

cross check
tables
  • johnt75's avatar
    johnt75
    3 years ago

    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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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's avatar
      akfir
      Helper V
      3 years ago

      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's avatar
        johnt75
        Super User
        3 years ago

        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