Forum Discussion

Robin_Lindstrom's avatar
Robin_Lindstrom
Copper Contributor
Apr 13, 2021

Wrong Filter Flow Direction when using Excel Power Pivot

Hello everyone!

I am having a problem which is doing my head in.

 

So in the file attached, you can see that I have a table for Package, a table for Arenas (Products), and a table of which arenas belong to which Packages. An Arena can belong to 1 or many Packages and a Package can include 1 or many Arenas, therefore I created a table between them where you simply add this information.

 

Now, I want to see which arenas belong to which Packages but the filter direction goes in the "wrong way". I want to be able to filter the Package table and see which arenas that belong to that package but as you can see, I get that all arenas are included in all packages (because of the filter direction).

 

If this was Power BI, I would change the direction of the relationship to "both" but this does not seem to be a function in Excel Power Pivot.

 

I need help 🙂

 

Kind Regards,


Kind Regards,

 

Robin

BI & Data Analysis
excel
Formulas and Functions

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 13, 2021

    Robin_Lindstrom 

    As variant you may add measure

    Count Arena:=CALCULATE(
   COUNTROWS(Arena),
   CROSSFILTER(Arena[ArenaCode],Arena_Package[ArenaCode],Both)
)

    Result is

     

    Grand Total is 5 since totally it's 5 different arenas appeared. In general depends on what do you need if modify.

    • Robin_Lindstrom's avatar
      Robin_Lindstrom
      Copper Contributor
      Apr 14, 2021

      Thank you Sergei!



      I will ask you the same thing I asked Riny. In Power BI, you can set the filter direction to bi-directional but that does not seem to be the case in Excel Power Pivot? Eventually, I want to build a bigger model but I am unsure if that will work in Excel if the filtering direction only goes one way. Do you have any experience regarding this?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 14, 2021

        Robin_Lindstrom 

        No, bi-directional is not supported in Power Pivot. CROSSFILTER() works instead, but you shall add it to each measure which uses such relationships.

        In general it's better to avoid bi-directional even in Power BI since decreases the performance, but it very depends on the model is that critical or not. 

    • Robin_Lindstrom's avatar
      Robin_Lindstrom
      Copper Contributor
      Apr 14, 2021
      Thank you Riny!

      In PBI, you can set the filter direction to bi-directional but that does not seem to be the case in Excel Power Pivot? Eventually, I want to build a bigger model but I am unsure if that will work in Excel if the filtering direction only goes one way. Do you have any experience regarding this?

Resources