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 ...
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
Community Support
Community SupportHI, 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