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 ...
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

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

    • akfir's avatar
      akfir
      Helper V
      3 years ago

      Perfect! thanks!

    • akfir's avatar
      akfir
      Helper V
      3 years ago

      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?