Apr 13 2021 11:26 PM
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
Apr 14 2021 12:26 AM
Apr 14 2021 12:36 AM
Apr 14 2021 10:46 PM
Let me say something about the basic of relationship between Tables.
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.
Apr 15 2021 03:17 AM
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.