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

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

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?