Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
)
)
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
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?
Perfect! thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |