Forum Discussion
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 ...
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
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!
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