I have a tricky problem which I hope someone has a good idea/hack for.
I have a products table which contains sales by a product name, month and a flag for if this is a top 300 product in that month.
I have a category table which is a mapping table of products to category.
Only top 300 products are ever mapped to a category, once a product is mapped it will stay so even if it drops out of the top 300 in following months.
My user says using the category filter is fine at all times but when including category in a group by we need to limit the product data to the top 300 products - they have their reasons which are not going to be changed - nor is the data.
In summary, we want to use categories for products which may no longer be in the top 300 however when analyzing results by category they want it to be fair.
Any ideas how this can be achieved? - I cannot filter the dimension because we want to populate the full list in the filter likewise for the same reason I cant only join on the top 300.