Forum Discussion
Will Power Pivot be enough?
Hello everyone,
I am about to build a data-model, but I am unsure whether I will be able to build it in Excel (Power Pivot) since the filtering direction can only be set to one direction. In contrast, I have built a data model in Power BI and there you can set it to bi-directional, but I don't think this function is available in Excel?
Is there an easy way to overcome this in Excel by using DAX? or should I simply create it in Power BI instead? I would like to go with Excel since the file will be used for some manual input as well.
Br,
Robin
4 Replies
Many-to-many still is not supported in PowerPivot. But that shall not be a showstopper, proper designed bridge tables and measures allow to receive desired result. There are a lot of posts related to M2M in Power Pivot, first what google shows
Many-to-Many relationships in PowerPivot - SQLBI
A mystifying and awesome solution for many 2 many | P3 Adaptive
By the way, above two resources, sqlbi.com and p3adaptive.com (former powerpivotpro.com) are quite useful, on them you may find answer practically on any related to DAX question.
- Data model is an object can be used for Power Pivot as well BI,,, what yo are trying to support it please edit your post & share some sample data along with expected output, will help us to fix it !!
- Hi Rajesh!
As of now, I do not have an example, it is more of a general question. I am just wondering if the uni-directional relationships will become a problem since you cannot set the relationship to bi-directional in Excel 🙂Let me say something about the basic of relationship between Tables.
- One to One:
- One to Many:
- Many to Many:
1. When both parent & child table has one common column setting the relationship. and no duplicate records, like ID in both tables, has Unique records.
2. When parent table has unique records ( like unique IDs) but child table has duplicate.
3. Many to Many works when both tables has duplicates for the Field ( IDs )setting relation between tables.
Using Data model,,, data diagram you can set relationship between tables.
