topic Re: cross checking between 2 tables in DAX Commands and Tips

cross checking between 2 tables

akfir — Thu, 20 Oct 2022 14:24:17 GMT

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

Re: cross checking between 2 tables

johnt75 — Thu, 20 Oct 2022 15:22:31 GMT

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

Re: cross checking between 2 tables

akfir — Fri, 21 Oct 2022 05:51:03 GMT

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!

Re: cross checking between 2 tables

johnt75 — Fri, 21 Oct 2022 08:32:33 GMT

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

Re: cross checking between 2 tables

akfir — Fri, 21 Oct 2022 10:51:18 GMT

Perfect! thanks!

Re: cross checking between 2 tables

akfir — Fri, 21 Oct 2022 21:05:45 GMT

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?