Multiple Filters (Slicers) on single column in fact table as Union (OR instead of AND)

Question

Hi,

I am currently working on a powerpivot-prototype to use powerpivot as the bi-frontend for Dynamics NAV. I am currently fighting a problem that arises from NAV's data structure.

To explain quickly: in order to store facts and the dimensions of each fact NAV uses four tables:

• facts (columns: entry no, amount, date etc.)
• dimension codes to store all possible groupings(columns: dimension code - entries could be customer group, cost object)
• dimension values to store the possible manifestations of each grouping(column: dimension code, dimension value - entries could be customer group; europe - customer group; usa

• dimension entry (combination of dimension value and fact - colums: entry no., dimension code, dimension value)

  Theoretically unlimited dimension values could be assigned to each fact.

   The following diagram shows the relationships - dim key is just the combination of dimension code and dimension value

Could someone help me how to create a simple powerpivot that would allow me to slice te data based on multiple dimensions, e.g. I would like to see the totals related to customer group = Europe and Cost Object = 10. I tried to duplicate the dimension tables in order to add two pairs of slicers but it seems that this only shows EITHER the sum for Customer Group = Europe OR the sum of Cost object = 10. What I really want is the intersection of both. Is this somehow possible or would I have to change my data structure?

Thanks a lot in advance!

Julian

Answer 1

Hi Julian

Not an answer to your issue yet, but I noticed in your diagram that your 'fact' table appears to actuallly be used as the 'lookup' table of the dimension entry table.  Lookup tables are normally the 'dimension' tables, not the facts.  Was this on purpose?

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Hi Javier,

In this case I have a many-to-many relationship between dimension entry and facts. The dimension entry stores multiple dimensions that belong to a certain fact. For example one fact (G/L-entry) can have 3 dimension entries:

• Dimension Code = Sales Territory, Dimension Value = Europe
• Dimension Code = Salesperson, Dimension Value = Julian
• Dimension Code = Item Group, Dimension Value = ebooks

Now I want to analyze my sales transaction by comparing the amounts filtered on different dimensions - e.g. all sales in territory xy with item group yz.

Answer 3

Hi,

For many to many relationship in PowerPivot, two notable posts on the subject can be found on Marco Russo’s and Alberto Ferrari’s blogs:

http://sqlblog.com/blogs/alberto_ferrari/archive/2010/10/19/powerpivot-and-many-to-many-relationships.aspx

http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx

---

Challen Fu

TechNet Community Support

Answer 4

an option would be to create one calculated column for each Dimension Code

e.g. for Column [Sales Territory] use the calculation "=IF('dimension values'[Dimension Code] = "Sales Territory"; 'dimension values'[Dimension Value]; "N/A")
doing this for all Dimension Codes allows you to create a slicer for each Dimension Code and you could filter on [Sales Territory]-column = "xy" in one slicer and use a second one to filter [Item Group]-column = "yz"

the only drawback would be that you see "N/A" in every slicer - though you may move it to the end by naming it "zzz" or whatever

hth,
gerhard

---

- www.pmOne.com -

Answer 5

Hi Julian,

I am still a bit confused with your data model.  The only many to many relationship on it is between 'dimension codes' and 'dimension entry' (with 'dimension values' as your bridge table).   However, the column 'dimension code' exists in both tables above.  It would appear you could 'flatten' out the model to only one table including all 'dimension' attributes (like only use the 'dimension entry' and facts table only).  Where does territory come in place? where is it in the data model?

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 6

Hi Javier,

The dimension-concept of NAV is a little bit strange what is why it is really confusing.

The Dimension Code table stores all "categories" that might be subject of analysis. One dimension code could be "sales territory", another one "sales channel". Dimension values on the other hand store possible values of each dimension - e.g. there could be 4 dimension values assigned to the dimension code "sales territory", North, South, West, East, and some dimension values assigned to dimension code "sales channel" (e.g. internet sales, retail sales etc.)

Each sales transaction (fact) now might also "have" multiple dimensions (called dimension entries) -e.g. one transaction of a customer in the territory "North" might be done through "Sales channel" "Internet Sales" what would lead to two dimension entries of that one fact-record.

I hope this makes it a bit clearer.

Answer 7

Hi Gerhard,

Thank you for your response.

I was able to proceed as suggested by you and added the columns to the dimension entry table.

However if I add these news columns as slicers to my pivot-table I am still not able to see those entries that are of customer group "usa" and location = "nyc" (it should retrieve entry no. 1 for that example). That behavior makes sense since the intersection of both is empty (even if both rows that match the criteria have the same entry no.) May I have misunderstood your idea?

I tried to add these new columns to my fact-table instead but I was not able to due to another problem that I posted about in another thread (http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/c3742f3d-f4cf-43fc-9b01-b32d5bfb31c8)

Regards,

Julian