Thanks in advance for any advice or insights you can offer.
Context:
Stores can sell one or more specialty products spread across two store stypes. If a consumer buys a speciality product the store type is speciality, but if a consumer buys a non-specialty product that same store type is categoryisn'
For example,
Widget speciality needs to be categorized as specialty when a widget is sold, but that same store could also be a dongle specialty store and would need to categorized as Speciality when a dongle is sold. And that same store sells myriad other products and when those are sold it needs to be "non-specialty." Table bleow as example.
Any thoughts on the best way to handle this so slicers and filters for KPI comparison acorss segments can be used?
My first thought was to use multiple dimension tables based on "product" so I never need to mess with a many to many relationship. But then I think I'd lose the ease of filtering by "specialty" product, since store type would be in different dimension tables. correct? Then I thought to use DAX with filtering to create separate groups, but I think that might also cause a loss of filtering at the product level b/c different groups would need to be used. Is there a solution I am missing?
Any other solutions or cautions?
| Store number | Speicality product | Store type Granular | Storetype General |
| 1 | dongle | Specialty | Non-specialty |
| 1 | widget | Speicalty | Non-Specialty |
| 1 | | Non-specialty | Non-Specialty |
