Is Bidirectional filtering really that bad?

[tmjones2]

I've watched many videos from Macro and Alberto explaining why bidirectional filtering is "hell" and I still can't figure out what's so bad about it if you know what you're doing. In their examples they never really draw the models up the way that I'm making mine, as they always have the tables connected in a big circle (typically with a fact table on each side). I used QlikView for many years and I'm building my models the same way that I would build them in there and I really haven't had any problems yet w/ models of ~30 tables and one fact table with ~10 different facts in it.

I'm basically building my models like the first image below (let's assume Operations is the fact table and in mine there are obviously a lot more tables, but this is basically what it looks like). The models they show are more like the second image (just pretend like the filter directions are all single). One important note is that I'm doing everything I can to consolidate my facts into a single fact table which enables this to work.

If you have some facts that are wildly different (or the fact tables are unbelievably huge as I will concede that bidirectional models are slower), I can see doing it their way but if your facts are mostly affected by the same dimensions and aren't a billion rows, it just seems like more of a pain to do it their way:

• Slicers won't filter properly
  • If I filter on a product that's only available in two states and my slicer still shows all the states, the users of my reports are going to be rightfully complaining about it
• DAX expressions seem more complicated (I could be wrong on this one as I'm not a DAX expert but when I see the expressions they write to do simple things it seems excessive)
  
  • I definitely have some measures that work easily b/c I have bidirectional on and if I didn't I would have to force it in the measure which seems unnecessary
• IMO it's HARDER to know how the filters will propagate when the models get big because b/c there are lines going in every possible direction and they are constantly overlapping
  • This is more referring to having 10 different fact tables vs. consolidating them into one fact table. In my models, it's extremely easy to figure it out because everything is connected (except the date table which is a different discussion)

I'm just having a hard time figuring out why my large models all seem to work perfectly and I'm doing exactly what the experts are telling me NOT to do. I suppose if you have zero idea what you're doing then sure bidirectional can be terrible but if you've been developing models this way for 10 years, is it factually worse?

[Anonymous]

@tmjones2 - 

I see a couple of issues with your model below:

1. There are 2 different paths, and therefore 2 different ways, that Supplier could filter FactContract. 

   -Supplier filters FactContract directly. (intuitive)

   -Supplier filters FactPrice, which then filters the Terminals to those which are listed for Suppliers in Fact Price. Finally, filter FactContract by this list of terminals. (unintuitive and wrong results, e.g. Contracts for other Suppliers would show up)

2. The Contract Price will be misleading if you filter on a Product, again because FactContract is not actually filtered by Product, but it is filtered by terminals associated with the product.

Cheers!

Nathan

[tmjones2]

Those are just models I grabbed off of Google images, just pretend like the fact table in the first image has multiple facts inside of it and the second image doesn't have any bidirectional filters (so that both models have two different facts). I don't see why the second model is any better, but I see multiple ways why it's worse.

[cnweke]

I've been researching and aplying bidirectional filters a lot recently and the core points are:

• As with everything: think before you enable it. It can be bad, but it might just be what you need.
• If you're doing 'straight' kimball there's not really a lot of danger of creating ambiguity. Whenever I model I try and stick to this as much as possible. 
• Propagating filters (aka synced slicers) is 100 % necessary it's stupid that this isn't a baseline feature.
• Your first model has no ambiguity, you can safely use bidirectional filters there. Your second one is extremely dangerous and unpredictable.
• Taking all of the following into consideration: if it's that important you might want to heavily denormalize your data to facilitate using it properly. I obviously do not have insight to your data but the last image you posted has products and suppliers. Are those products delivered by those suppliers? if so just denormalize, make it one dimension and call it day.

I think his 'stance' is a mix of fearmongering, clickbait and plain truth. If you look at the end of this article however you'll see that he takes a more nuanced tone. https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/?platform=hootsuite 'There are a few scenarios where the power of bidirectional cross-filter really shines. However, these need to be leveraged carefully making sure that the model does not become ambiguous because of the bidirectional relationship. '