Search code examples
relational-databaseexcel-2010powerpivotdax

Power Pivot 2010: DAX, Dynamic Groups with Overlap

How do I use Power Pivot to summarize data in groups which are defined in a separate, non-relatable table

I'm analyzing a database that has the following tables:

Sales
  Store
  Category
  Units
  Sales
Stores
  Store
  address
  etc
StoreGroups
  Store
  Group

A store can be in multiple groups (i.e. store B762 is in NW group & control_group) hence the StoreGroup table - where the two fields together make the primary key. Therefore, I can't relate StoreGroups to my Sales table, because both have duplicate Store values.

Right now all stores are being reported in each group: PivotTableScreenshot

to confirm, if a store is in two groups, its sales should get counted for BOTH groups i.e. control group and NWRegion.

I've tried to adapt this DAX example mentioned below but have not been successful: http://www.daxpatterns.com/dynamic-segmentation/

Solution

  • You have a many-to-many relationship between stores and groups.

    You should be able to create a relationship from the store in StoreGroups and the Store in Stores (StoreGroups is a bridge table).

    If you can post a link to some sample data, that would be helpful.

    After doing that, you can read start to read about writing DAX formulas for many-to-many scenarios here. Be sure to also read in the comments, especially the one from Marco Russo.