This requirement effectively goes against the foundations of tabular models, but interested if anyone has a solution. What I'd like to do is apply a filter on a column and have that filter context apply to another column at a higher level to dynamically calculate level of detail totals. Take the below dataset as example:
| Category | Brand | Product | Sales |
Food | Oreo | A | 10 |
| Food | Heinz | B | 20 |
| Food | XYZ | C | 30 |
| Beverage | Coke | D | 15 |
| Beverage | Pepsi | E | 25 |
| Beverage | XYZ | F | 35 |
What I'd like to do is allow users to apply a filter on Brand where, for example, Brand = 'XYZ' and it would return Category sales which would sum Food + Beverage to be 135 because Brand XYZ exists in both categories. In comparison, if I were to filter where Brand = 'Pepsi' then the Category sales measure would just return 75 for Beverage. The reason I cannot use an ALLEXCEPT() is because I don't want to force a selection on category, I want to be able to solely place a filter selection on Brand and have that infer the Category totals whether the mapping is one-to-one or one-to-many.
The goal ultimately is to be able to allow users to interact with a minimum number of filters while still being able to look at totals and averages across a category without having to explicitly select category in a filter. Thanks!
