Forum Discussion
Issues with filter propagation
What you are describing here is the concept of expanded tables which is basically that the table on the one side of a many to one relationship, includes all the columns from the many side. So in your example, the entire Sales table are its' columns + all columns from the Product table.
When you filter the Product table by the entire Sales table, the resulting table is that of products which have been sold. Which is why you get the same amount of rows in the products table that are in the sales table. But that only works when you use an entire table as a filter ( and remember filters are tables...), and entire table is the tables actual columns you see plus all the ones that are on the many side. And you are corret, this does make it appear that filter flow up-hill. Which they do not, but sure seems like it.
That is quick explanation of what is happening here and really does require some more in-depth understanding of the theory to really get what is going on here. Not sure what books you have but if this is the type of thing you are wanting/needing to understand, I'd suggest picking up the Definitive Guide to Dax for sure.
No problem at all. It's not an overly complex subject, it can just be alot to remember. I reference that book quite a bit. Good luck :)
For those with no access to the book, here's a interesting article on the topic by the same authors:
AlB Did you find out a way how not pass filter on dimension table when it is applied on the fact table. I am facing a similar problem. direction of the relationship is from dimension to fact.
The fact table will not filter the dimension table unless you use the construct I described earlier, i.e., the expanded table. Like I said, check this article, it explains the topic quite well:
https://www.sqlbi.com/articles/expanded-tables-in-dax/
I'm assuming you have an unidirectional relationship, of course.
AlB Yes I have the unidirectional relationship. I have gone through the article but still, something missing.
I have posted my question on MSDN. see if you can catch the issue.
Thanks,
