I can't even begin to describe how happy I am that i found this video. I was about to redesign my whole model to only use one fact table, and all I had to do to get the filters to sync was add a measure. Thank you so much! Great video.
My company created a bunch of data models where every possible relationship is bi-directional before hiring me... I told them that I won't touch them and I won't take responsibility for the numbers they display and your video will be a very good example why you need people who knows how to build data models as it's not simply drag and drop. Thank you very much for a very informative and well presented video :)
While looking for a solution for my model that has a lot of bidirectional relationships, I meet Alberto with the amazing video and the answer for my big question. Thank you for your great works, I always appreciate your contribution to make DAX easier to use
in Summation WOW -- the really explains why you should ALMOST never use bi-directional relationships for purposes of filtering and why you probably should ALMOST NEVER trust a model that does .. in the back of my mind I was trying to figure out how the ambiguity impact things -- great walk through THANK YOU
I always had issues with ambiguous models. and what I concluded from my report is to never use bi directional filter. I never knew what was the reason. now I got I must appreciate your efforts 👍👍
Just as I was looking at this, I found this video and it answered everything. Thanks Alberto. I think PBI should give a warning if it detects alternate fllter paths. If it would be difficult to explain such ambiguity to a non-technical person, then the design should be changed. The stakeholders/consumers will lose trust and confidence if something is happening that cannot be understood.
Amazing explanation. Self service BI report developers MUST watch this video. I had to explain this to lot of people and now I can just guide them here 👍 Thank you.
Great video, very clear and well done, thanks Mr. Ferrari. I would just emphasise that that the problem presented doesn't come only from using the bidirectional relationship, but it comes form using the bidirectional relationship IN a loop, because in a purely star schema no ambiguity is created when using bidirectional relationships. This would be just to reinforce what is already a great explanation!
Thank you so much Sir for posting this valuable video. I can't express my gratitude towards you that how much I was needed this video. Thank you again 😊
this has definetely earned you a new sub, you really nailed every part of the clarification needed throughout the tutorial. thanks a lot for this helpful tutorial. I have to learn from you a lot!
Thank you for this clear explanation (love the handwritten arrows on top of the modeling view !). Aside from the ambiguity arising from the use of a bidirectional relationship (which I manage to keep away from), I discovered a neat way to show only relevant choices in slicers !
"Welcome to Hell!" I was laughing so much that I had to pause the video to put in this comment! I think the real key to understand why adding the filter to the slicer works is that a slicer is just another visual. All the usual cross-visual filtering rules apply. It's one of those things that is obvious if you think about it at all, but it is easy to fall into the trap of thinking a slicer is just a different way to show a filter. I did.
Such an important video. I keep seeing bi-directional relationships in model and model and my concerns usually fall on deaf ears. Great to have a very detailed video to point people to now. Something not discussed here but I thought I remembering seeing in the video training that SQLBI does - the bidirectional relationships can also slow performance because they tend to keep the model from being able to choose the optimal path.
Very elegantly explained. Using the side Filter Pane would not have occured to me. I would have edited the Sales Measure by using CROSSFILTER. But I think the Filter Pane, does the job just as well!
I was facing the same issue and was stuck trying to figure out how bidirectional works from the last 2 days. I tried to check if it works like sql joins but still the result was ambiguous showing a greater value than that in sql. Thanks a lot for making this content. For slicer u used 'is not blank' filter. but i have direct column from 2 unrelated table(not connected directly, although connected through other tables, but flow non-unidirectional) and that too numerical(sum of amount). But now i hope i will figure it out how is it working in my case. thanks a lot lot sir. its much much informative and helpful.
Notes for my future revision 6:05 A bi-directional relationship created two paths that link Date and Purchases. Yellow and Red. That's why the model is ambiguous. Yellow: direct link. Red: through Sales and Product. // A table can flow to another table by relationship via only one path. Multiple paths create ambiguity. // Here, Purchase table has relationships to both Date and Product. //These relationships affect filter going into Purchase, which is usually OK and is a typical star scheme. // However, with the bi-direction link into Product, Date now also reaches the Purchase table through another path (Date-Sales-Product). There are two separate paths from Date to Purchase. IF ANY TABLE REACH ANY ANOTHER TABLE THROUGH MORE THAN ONE PATH, THE DATA MODEL RISK BEING AMBIGUOUS 8:10 Sales is filtered by Customer and Dates. Which one being used? 8:38 Purchase can be calculated by including: 1. Customer and indirect Date filter 2. Direct Date filter 3. Customer and indirect Date filter (with direct date filter removed)
4 роки тому+1
Love this one, a true best pratice that can change life and model design
Thank you for providing this detailed video on ambiguous models. There isn't a lot of information out there, so this is very much appreciated. Though, I only see a problem if you have a model with two fact tables. If you keep things simple (single fact table, multiple dimension tables), there should be no opportunity for multiple paths, correct? Therefore the model wouldn't be ambiguous?
Hi Alberto. I think it would stand to reason to also mention that if someone uses USERELATIONSHIP and/or CROSSFILTER, they should always make sure that such a manipulation does not transforms the model into an ambiguous one.
Thank you for a great video. I'm a newbie and am a bit confused as to how the direction of filtering goes when you apply a filter on the visual in the end of the video. It seems the cross filter direction does not affect that, since a filter on sales has an effect on product.
Beside limited cases (like implementing security patterns or physical implementation of M2M cardinality relationships - see sqlbits.com/Sessions/Event18/Different_types_of_many-to-many_relationships_in_Power_BI), it's almost never a good idea to enable bidirectional filters in the model, while it makes perfect sense in specific measure where you can control the side effects of it.
Hello Alberto, thanks for putting this video together. Very helpful! I do have a question about the sales measure you used and the calculation it performs. Would you be able to share?
Thanks a ton for the great explanation!! I tried it out and worked perfectly! However, if I want to influence from lets say the customer dimension, a measure in the products table, then this does not seem to work. Would you be so kind to help?
Thank you for this useful video. Would it be fair to say that the ambiguity issue which you describe would not be an issue in a Star Schema w. one fact table (where Dimensional tables only reach to each other via the Fact table)?
Alberto, can you read this thread on the same topic. If you have any thoughts please comment. www.reddit.com/r/PowerBI/comments/kqsy0v/filter_direction/ Thanks for all the insight you have been sharing.
You should avoid bidirectional filters in the model, use them in the measures only if possible. Best practices, workarounds, and explanations here: www.sqlbi.com/articles/syncing-slicers-in-power-bi/ www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
thank you so much for this video. It was of great help. What if the "Color" is not in the slicer but in the visual and the user wants the visual filtered based on selection in "customer" slicer? how do i achieve that?
Excellent video Alberto! Thank you very much. I'll send this link to all participants of my workshops and trainings! One question: What tool did you use to draw on your data model?
In this video am using Epic pen on the desktop, with Virtual tablet on the tablet, to use its pen. Worked ok... but not yet perfect, some lag and not as fluid as I'd like. In the next videos I stopped using Epic pen and I preferred to draw directly on the tablet, on top of screenshots. Much more fluid and much easier to use. Still searching for the perfect setup, anyway
Great, thx, great explanation... there are dax teachers who tell their students to put all the crossfilter in relations in both directions. Here is the reason why you never do this. Great alberto. ( who rides in a maserati! 😄)
Continent is from Customer Table, it gives information about which continent the customers are from. Why would you want to filter purchase table by Continent in the Customer table? What is the relation between customer continent and purchase? Help me understand
Brutal! I spent 4 sleepless nights because of that... thank you so much!
I can't even begin to describe how happy I am that i found this video. I was about to redesign my whole model to only use one fact table, and all I had to do to get the filters to sync was add a measure. Thank you so much! Great video.
Hi, Would you mind sharing more please? What type of measure did you create to get the filters to sync please?
man wow, how beautifully he explains and all the tools he uses. a true master.
could you please help me in understanding why it worked after applying the visual filter?
Thank you Mr. Ferrari, I love the way you raise the problem, analyze it and give the solution. Very clear and useful!
My company created a bunch of data models where every possible relationship is bi-directional before hiring me...
I told them that I won't touch them and I won't take responsibility for the numbers they display and your video will be a very good example why you need people who knows how to build data models as it's not simply drag and drop.
Thank you very much for a very informative and well presented video :)
While looking for a solution for my model that has a lot of bidirectional relationships, I meet Alberto with the amazing video and the answer for my big question. Thank you for your great works, I always appreciate your contribution to make DAX easier to use
in Summation WOW -- the really explains why you should ALMOST never use bi-directional relationships for purposes of filtering and why you probably should ALMOST NEVER trust a model that does .. in the back of my mind I was trying to figure out how the ambiguity impact things -- great walk through THANK YOU
I always had issues with ambiguous models. and what I concluded from my report is to never use bi directional filter. I never knew what was the reason.
now I got
I must appreciate your efforts 👍👍
This is a very neat trick to avoid ambiguity!! Thanks! SQLBI is on top of my top 5 resources!
Just as I was looking at this, I found this video and it answered everything. Thanks Alberto.
I think PBI should give a warning if it detects alternate fllter paths.
If it would be difficult to explain such ambiguity to a non-technical person, then the design should be changed. The stakeholders/consumers will lose trust and confidence if something is happening that cannot be understood.
Excellent. So well explained. I would have never understood the consequences of bidirectional if I didn't bump into this video. Thanks a lot.
I noticed the name of the final tab called "Welcome to hell" :D
Clearly explains the issue! Thanks for the content, very useful!
This is the best video I’ve ever seen on ambiguity and relationships. Thank you!
Amazing explanation. Self service BI report developers MUST watch this video. I had to explain this to lot of people and now I can just guide them here 👍 Thank you.
Sono rimasto stupefatto dalla chiarezza della spiegazione. Complimenti davvero
Amazing explanation. Fixed the problem I didn't even know how to frame a question around.
What a good teacher you are, Alberto! Fantastic explanation! Tante grazie!
Great video, very clear and well done, thanks Mr. Ferrari. I would just emphasise that that the problem presented doesn't come only from using the bidirectional relationship, but it comes form using the bidirectional relationship IN a loop, because in a purely star schema no ambiguity is created when using bidirectional relationships. This would be just to reinforce what is already a great explanation!
True - but you have to be sure you'll not add other fact tables to the same model sharing the same dimensions... in the future
Thank you so much Sir for posting this valuable video. I can't express my gratitude towards you that how much I was needed this video.
Thank you again 😊
I discovered the channel today and... congratulations on such quality content! Learning a lot here!
this has definetely earned you a new sub, you really nailed every part of the clarification needed throughout the tutorial. thanks a lot for this helpful tutorial. I have to learn from you a lot!
Thank you for this clear explanation (love the handwritten arrows on top of the modeling view !). Aside from the ambiguity arising from the use of a bidirectional relationship (which I manage to keep away from), I discovered a neat way to show only relevant choices in slicers !
"Welcome to Hell!" I was laughing so much that I had to pause the video to put in this comment! I think the real key to understand why adding the filter to the slicer works is that a slicer is just another visual. All the usual cross-visual filtering rules apply. It's one of those things that is obvious if you think about it at all, but it is easy to fall into the trap of thinking a slicer is just a different way to show a filter. I did.
Amazing sir! The solution is so elegant that I have to clap when I saw it!
Such an important video. I keep seeing bi-directional relationships in model and model and my concerns usually fall on deaf ears. Great to have a very detailed video to point people to now. Something not discussed here but I thought I remembering seeing in the video training that SQLBI does - the bidirectional relationships can also slow performance because they tend to keep the model from being able to choose the optimal path.
yes bidirectional filters can also affect performance, but first of all you need to get right numbers!
You provided much better insight about bidirectional filter than Microsoft documentation / training.
Thank you very much for these videos Alberto and SQLBI! Learning a lot about DAX!
Very elegantly explained. Using the side Filter Pane would not have occured to me. I would have edited the Sales Measure by using CROSSFILTER. But I think the Filter Pane, does the job just as well!
yes, but you have to do anyway the 'trick of filter pane' if you want to "synchronize" the two slicers...so two birds with one stone..
Excelente exposición!!! Eres lo máximo Alberto Ferrari.
Really easy to understand, as always great job. Thank you SQLBI team for your amazing work.
Really Really Superb explanation, I love your videos .. one request if any monthly enable SQLBI its really helpful to me
I was facing the same issue and was stuck trying to figure out how bidirectional works from the last 2 days. I tried to check if it works like sql joins but still the result was ambiguous showing a greater value than that in sql. Thanks a lot for making this content. For slicer u used 'is not blank' filter. but i have direct column from 2 unrelated table(not connected directly, although connected through other tables, but flow non-unidirectional) and that too numerical(sum of amount). But now i hope i will figure it out how is it working in my case. thanks a lot lot sir. its much much informative and helpful.
Thanks! This quick solution of visual filters is possible only in Power Bi. In Excel, we use 'Crossfilter' in the measure formula.
Thank you Alberto i have been looking at that trick for weeks since i have similar business problem. Glad i came a croos the video. 🤗
Fantastic explanation and presentation! Thank you so much.
Excellent explanation! Congrats for such a great work!
Hello Alberto. Have just found your channel. Awesome video. Just one request - pleaseeee do more examples on ambiguity. Really helps us the beginners.
Thank you very much for these videos Alberto and SQLBI
Wow. I am taken aback by the explanation. Much love!
This is a classic lesson. Thank you! Big up!
Nice explanation, i now finally understand ambiguity and now why i can not use bidrectional filters
Thank you a lot. Really happy to find this channel.
This is a great! I need to try this next time I come across situation like this.
Amazingly explained such a complex scenario.. Kudos!
Thank you for clear and precise explanation!
Notes for my future revision
6:05
A bi-directional relationship created two paths that link Date and Purchases. Yellow and Red. That's why the model is ambiguous.
Yellow: direct link.
Red: through Sales and Product.
// A table can flow to another table by relationship via only one path. Multiple paths create ambiguity.
// Here, Purchase table has relationships to both Date and Product.
//These relationships affect filter going into Purchase, which is usually OK and is a typical star scheme.
// However, with the bi-direction link into Product, Date now also reaches the Purchase table through another path (Date-Sales-Product). There are two separate paths from Date to Purchase.
IF ANY TABLE REACH ANY ANOTHER TABLE THROUGH MORE THAN ONE PATH, THE DATA MODEL RISK BEING AMBIGUOUS
8:10
Sales is filtered by Customer and Dates. Which one being used?
8:38
Purchase can be calculated by including:
1. Customer and indirect Date filter
2. Direct Date filter
3. Customer and indirect Date filter (with direct date filter removed)
Love this one, a true best pratice that can change life and model design
Oh my goodness, such a beautiful explanation. Wow Man!
Useful content, poetic explanation...Suberb teacher
Omg! Good problem, great explanation and excellent solution
Thanks you to make it so clear and easy ! without avoiding to dig in detail :)
Make times fresher to me haha
Great! Really really great examples and explanation!
Great Explanation Sir!! Keep it up👍
Thank you for providing this detailed video on ambiguous models. There isn't a lot of information out there, so this is very much appreciated.
Though, I only see a problem if you have a model with two fact tables. If you keep things simple (single fact table, multiple dimension tables), there should be no opportunity for multiple paths, correct? Therefore the model wouldn't be ambiguous?
Very insightful! Thanks for the knowledge master🙏
So, the filter actually applies the crossfilter at the backend, this was not known to me , thanks a lot
Hi Alberto. I think it would stand to reason to also mention that if someone uses USERELATIONSHIP and/or CROSSFILTER, they should always make sure that such a manipulation does not transforms the model into an ambiguous one.
Fantástico! Thank you MAESTRO!
Wow. This is so informative and useful.
Very well explained. Thanks Alberto.
Mind blown! I need to try this. Thank you
Great explanation. You just earned a new sub sir.
Thank you for a great video. I'm a newbie and am a bit confused as to how the direction of filtering goes when you apply a filter on the visual in the end of the video. It seems the cross filter direction does not affect that, since a filter on sales has an effect on product.
I LOVE YOU Alberto !!
YOU ARE THE LEGEND
Absolutely perfect, thank you so much for this video
Very nicely explained. Thank you so much.
This is amazing, thank you so much for this video!
Great, thanks! Would be interesting if you have a video on when is a good time to use bi-directional relationships?
Beside limited cases (like implementing security patterns or physical implementation of M2M cardinality relationships - see sqlbits.com/Sessions/Event18/Different_types_of_many-to-many_relationships_in_Power_BI), it's almost never a good idea to enable bidirectional filters in the model, while it makes perfect sense in specific measure where you can control the side effects of it.
Alberto is the "data monster"!
This video is great but I'm not allowed to apply the measure on a visual if the visual is a card
Such a nice explanation !!
Hello Alberto, thanks for putting this video together. Very helpful!
I do have a question about the sales measure you used and the calculation it performs. Would you be able to share?
Excellent Video as Always!!
This is very helpful, thanks!
this video islike a movie of DAX !
I couldn't understand why filtering could make the data sync. Can you give me a simple example?
By the way, Great video, thanks a lot.
Really loved the tab you named "Welcome to Hell" 😀😀😀😀
Your videos are amazing.
Thanks a ton for the great explanation!! I tried it out and worked perfectly! However, if I want to influence from lets say the customer dimension, a measure in the products table, then this does not seem to work. Would you be so kind to help?
Thank you for this useful video. Would it be fair to say that the ambiguity issue which you describe would not be an issue in a Star Schema w. one fact table (where Dimensional tables only reach to each other via the Fact table)?
Yes, but it will appear the day you add another fact table, and at that point removing the bidirectional filter would break existing reports.
wonderful explanation... thank you...😀
Good video.Actually what happen in the behind when you add sales measure to the filters as not blank.Can you elaborate?
Ciao, friends! Enjoy DAX! Thanks for sharing
Fantastic video!
Excellent Example of Crossfilter
Thanks for explaining this, much appreciated.
Alberto, can you read this thread on the same topic. If you have any thoughts please comment.
www.reddit.com/r/PowerBI/comments/kqsy0v/filter_direction/
Thanks for all the insight you have been sharing.
You should avoid bidirectional filters in the model, use them in the measures only if possible.
Best practices, workarounds, and explanations here:
www.sqlbi.com/articles/syncing-slicers-in-power-bi/
www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
I guess sales of zero amounts are uncommon but would it not be better so have a measure called "sales exists" and filter on that instead?
thank you so much for this video. It was of great help. What if the "Color" is not in the slicer but in the visual and the user wants the visual filtered based on selection in "customer" slicer? how do i achieve that?
Would've liked to see the "welcome to hell" calculated metrics before and after removing the both directions relationship.
Cool. And yet so simple.
Love the sheet name “Welcome to hell” 😂
Literally busted out laughing when I saw that!
Excellent Video! Thanks!
awesome explanation, thank U
It's really cool. Thanks for sharing.
Can someone please explain why filtering the color slicer by non-blank sales gives us a dynamic slicer? I can't seem to wrap my head around the idea.
Excellent video Alberto! Thank you very much. I'll send this link to all participants of my workshops and trainings! One question: What tool did you use to draw on your data model?
In this video am using Epic pen on the desktop, with Virtual tablet on the tablet, to use its pen. Worked ok... but not yet perfect, some lag and not as fluid as I'd like. In the next videos I stopped using Epic pen and I preferred to draw directly on the tablet, on top of screenshots. Much more fluid and much easier to use. Still searching for the perfect setup, anyway
Great, thx, great explanation... there are dax teachers who tell their students to put all the crossfilter in relations in both directions. Here is the reason why you never do this. Great alberto. ( who rides in a maserati! 😄)
It hurts to know that someone promotes bad practices teaching that!
Finally understood it right!!
Holy crap I never would have thought of that in a million years! Thanks!!
Continent is from Customer Table, it gives information about which continent the customers are from. Why would you want to filter purchase table by Continent in the Customer table? What is the relation between customer continent and purchase? Help me understand
Indeed, that's the problem introduced by the bidirectional relationship - you don't want that to happen at all!
Great video, thanks!