Will Power Pivot be enough?

%3CLINGO-SUB%20id%3D%22lingo-sub-2271699%22%20slang%3D%22en-US%22%3EWill%20Power%20Pivot%20be%20enough%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271699%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20about%20to%20build%20a%20data-model%2C%20but%20I%20am%20unsure%20whether%20I%20will%20be%20able%20to%20build%20it%20in%20Excel%20(Power%20Pivot)%20since%20the%20filtering%20direction%20can%20only%20be%20set%20to%20one%20direction.%20In%20contrast%2C%20I%20have%20built%20a%20data%20model%20in%20Power%20BI%20and%20there%20you%20can%20set%20it%20to%20bi-directional%2C%20but%20I%20don't%20think%20this%20function%20is%20available%20in%20Excel%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20an%20easy%20way%20to%20overcome%20this%20in%20Excel%20by%20using%20DAX%3F%20or%20should%20I%20simply%20create%20it%20in%20Power%20BI%20instead%3F%20I%20would%20like%20to%20go%20with%20Excel%20since%20the%20file%20will%20be%20used%20for%20some%20manual%20input%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBr%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERobin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2271699%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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.