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!