Will Power Pivot be enough?

Contributor

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
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

@Robin_Lindstrom 

Let me say something about the basic of relationship between Tables.

  1. One to One:
  2. One to Many:
  3. 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.

 

You may check this...  ☺

@Robin_Lindstrom 

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.