Forum Discussion
Filter multiple rows against another table using less than
Hi Anonymous ,
I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:
The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:
Pre-Launch Contacts = VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0) RETURN CALCULATE( SUM('CS Data'[Contacts Answered]), FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))You can see that when you cross filter this with the YW of the product dates you get the results you need.
Hi Miguel,
Thanks for the response. I have amended the Year-Week columns in each table (of my actual PBIX file, not the sample) and this has not solved the issue unfortunately. To be clear, I padded the week number so that it would display as 6 digits (e.g. 202108 or 202006).
I did also realise after creating the sample that there was not much data which matches with the product launches. I only included a small sample since I am working with data on a weekly basis going back to the beginning of January 2019. I have now amended the sample file for product activities.
Hi Anonymous ,
Still not understanding what is the launch date your product activity starts on week 17 and yuor first contact is also on the week 17 so no contacts before the first activity.
Sorry for making this questions but I'm not understanding the model to what you consider the week launch of the product that is the point that you need to compare to make all the counts that you need.
Hi Anonymous ,
I have copy paste some lines on the contacts and changed the year this gave me the folllowing values per week:
The cumulative values are the ones until that week (excluding current week) this table was made only making the use of the contacts table now making use of the measure I have sent earlier:
Pre-Launch Contacts = VAR ProductYW = FIRSTNONBLANK('Product Activity'[YW],0) RETURN CALCULATE( SUM('CS Data'[Contacts Answered]), FILTER(ALL('CS Data'[YW]),'CS Data'[YW] < ProductYW))You can see that when you cross filter this with the YW of the product dates you get the results you need.
I had made a bit of an error in the sample files provided as I limited some of the data which was available.
In my real world file, I have weekly data which goes back to January 2019 (201901) and so the measure should be summing all contacts made in the weeks from 201901 until the product launch date (e.g. 202117).As I have said before, the week comparisons work when I use a = but if try to find a sum of contacts before (<) or after (>) then i do not get any results.
Thank you for all of your time and effort Miguel! Turns out I had the same measure as you have in your example solution and it turns out that I had a stray relationship connection which was not necessary but was messing up the measure showing the correct result (for some reason).
Thank you again!