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.
Hi,
Actually this has to do with order of evaluation of arguments
"The order of evaluation of the parameters of a function is usually the same as the order of the parameter: the first parameter is evaluated, then the second, then the third, and so on. This is always the case for most of the DAX functions, but not for CALCULATE and CALCULATETABLE. In these functions, the first parameter is evaluated only after all the others have been evaluated. If you come from a C# background, you can think to the first parameter as a C# callback function, which will be called only later, when its result will be really required."
Above Excerpt from this article by Italian Maestros
https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/
Thanks very much for the switft reply.
I have re-read the article you mention but i do not quite see how it would be relevant here. In the expression
Table1=CALCULATETABLE ( 'Product', Sales )
the filter argument is evaluated first, ok. It is the full Sales table. Then it is applied to the first argument. There is a many-to-1 relationship between Sales (many) and Products (1). Filters do not propagate from the many to the 1 side so the Products table should not be affected by the filter. That "uphill" propagation seems to be happening though, like I explained in the inital post. I don't understand what's going on.
Thank you
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.
Anonymous
Cool. Thanks a lot Nick.
I am actually a few pages away from the subchapter that describes expanded tables in the Definitive guide to DAX. I'll read it and see what I can make of it. I might get back to you with some questions :smileyhappy:
Thanks so much for pointing me in the right direction. This had been bothering me for some time now.
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 :)