Wrong Filter Flow Direction when using Excel Power Pivot

%3CLINGO-SUB%20id%3D%22lingo-sub-2270300%22%20slang%3D%22en-US%22%3EWrong%20Filter%20Flow%20Direction%20when%20using%20Excel%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270300%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20having%20a%20problem%20which%20is%20doing%20my%20head%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20in%20the%20file%20attached%2C%20you%20can%20see%20that%20I%20have%20a%20table%20for%20Package%2C%20a%20table%20for%20Arenas%20(Products)%2C%20and%20a%20table%20of%20which%20arenas%20belong%20to%20which%20Packages.%20An%20Arena%20can%20belong%20to%201%20or%20many%20Packages%20and%20a%20Package%20can%20include%201%20or%20many%20Arenas%2C%20therefore%20I%20created%20a%20table%20between%20them%20where%20you%20simply%20add%20this%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20I%20want%20to%20see%20which%20arenas%20belong%20to%20which%20Packages%20but%20the%20filter%20direction%20goes%20in%20the%20%22wrong%20way%22.%20I%20want%20to%20be%20able%20to%20filter%20the%20Package%20table%20and%20see%20which%20arenas%20that%20belong%20to%20that%20package%20but%20as%20you%20can%20see%2C%20I%20get%20that%20all%20arenas%20are%20included%20in%20all%20packages%20(because%20of%20the%20filter%20direction).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20was%20Power%20BI%2C%20I%20would%20change%20the%20direction%20of%20the%20relationship%20to%20%22both%22%20but%20this%20does%20not%20seem%20to%20be%20a%20function%20in%20Excel%20Power%20Pivot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EKind%20Regards%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-2270300%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%3CLINGO-SUB%20id%3D%22lingo-sub-2270489%22%20slang%3D%22en-US%22%3ERe%3A%20Wrong%20Filter%20Flow%20Direction%20when%20using%20Excel%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270489%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798894%22%20target%3D%22_blank%22%3E%40Robin_Lindstrom%3C%2FA%3E%26nbsp%3BUse%20the%20ArenaCode%20from%20the%20Arena_Package%20table%20in%20stead%20of%20the%20Arena%20table.%20I%20believe%20that%20gives%20you%20what%20you%20need.%20Two%20packages%20showing%20the%20Arenas%20that%20are%20included%20in%20each%20of%20them.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

7 Replies

@Robin_Lindstrom Use the ArenaCode from the Arena_Package table in stead of the Arena table. I believe that gives you what you need. Two packages showing the Arenas that are included in each of them. See attached.

@Robin_Lindstrom 

As variant you may add measure

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

Result is

 image.png

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

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?

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?

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

Thanks for your time and input Sergei!