Forum Discussion
Many to many relationship date filtering
Edit: Made post more readable, sample pbix file:
https://wetransfer.com/downloads/47f25500d60a96f3fd42a48335ce3c1220181218093545/c75f94
Hi,
I cant seem to figure out how to do row by row filtering between dates on a many to many relationship setup and any help would be greatly appreciated. I currently have a many to many relationship setup (see the image below).
The emails table has email campaign information. Some fields here are "EmailName", "AccountID" and the "SendDate" of each email (one email is send to multiple AccountID's, more importantly the same "EmailName" is being send at differing "SendDate" 's).
The order table contains order information from consumers. Some fields here are "AccountID", "Ordernumber" and "OrderDate". One account can have many orders that all have different order dates.
Now what I want to do is the following: For each email send, I want to see which people made orders with an "OrderDate" after the "SendDate" and count them, so that per email I have a total amount of orders following the email send. I have tried this with a measure using calculate a OrderDate>Max(SendDate) filter() but this does then not take into account that the same email is being send at different times, furthermore PowerBI ran into memory issues with these large tables and using measures in measures. Again, any help is greatly appreciated!
Example of desired output table:
EmailName, Orders_Within_1_week
Email 1, 0
Email 2, 2
Email 3, 2
HI, Anonymous
After my test on your pbix file, you could try this way as below:
Step1:
Add a new AccountID for Orders table
new AccountID = Orders[AccountID]
Step2:
Use this formula to create a Orders_Within_1_week measure
Orders_Within_1_week = var _table1=SUMMARIZE(Emails,Emails[AccountID],Emails[EmainName],Emails[SendDate]) var _table2=SUMMARIZE(Orders,Orders[new AccountID],Orders[OrderNumber],Orders[OrderDate]) var _table3=FILTER(GENERATE(_table1,_table2),AND([AccountID]=[new AccountID],DATEDIFF([SendDate],[OrderDate],DAY)>=0&&DATEDIFF([SendDate],[OrderDate],DAY)<=7)) return CALCULATE(COUNTAX(_table3,[OrderNumber]))+0
Result:
Best Regards,
Lin
5 Replies
v-lili6-msftCommunity Support
HI, Anonymous
Could you please share a simple sample pbix file or some data sample and expected output. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi,
Here is the link to an example pbix file:
https://wetransfer.com/downloads/47f25500d60a96f3fd42a48335ce3c1220181218093545/c75f94
The output should be the following table:
EmailName, Orders_Within_1_week
Email 1, 0
Email 2, 2
Email 3, 2Thanks!
- v-lili6-msft
HI, Anonymous
After my test on your pbix file, you could try this way as below:
Step1:
Add a new AccountID for Orders table
new AccountID = Orders[AccountID]
Step2:
Use this formula to create a Orders_Within_1_week measure
Orders_Within_1_week = var _table1=SUMMARIZE(Emails,Emails[AccountID],Emails[EmainName],Emails[SendDate]) var _table2=SUMMARIZE(Orders,Orders[new AccountID],Orders[OrderNumber],Orders[OrderDate]) var _table3=FILTER(GENERATE(_table1,_table2),AND([AccountID]=[new AccountID],DATEDIFF([SendDate],[OrderDate],DAY)>=0&&DATEDIFF([SendDate],[OrderDate],DAY)<=7)) return CALCULATE(COUNTAX(_table3,[OrderNumber]))+0
Result:
Best Regards,
Lin
- v-lili6-msft
hi, Anonymous
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
Hi v-lili6-msft this indeed works and my apologies for getting back to you so late, however with the large dataset im working with this turns out to be too slow to work with, hence I eventually just did the joins in my SQL queries. Thank you for your time!