Forum Discussion
Issues with filter propagation
Hi all,
I am running some tests related to filter propagation on the AdventureWorks DB. There is a many-to-1 relationship between Sales (many) and Products (1). The filters should thus propagate from Products to Sales and not otherwise (as explained in many DAX books/blogs). On Power BI Desktop I create Table1 as follows:
Table1 = CALCULATETABLE(Products; Sales)
The Products table has 397 rows and I'd therefore would expect Table1 to have also 397 rows. However, Table1 has 158 rows. This is the number of products that appear in the Sales table. In fact, Table1 is the same as Table2, defined as follows ([Sales Amount] is just a measure that calculates the total sales):
Table2=FILTER(Products; [Sales Amount] > 0)
It seems then that in Table1 Sales is actually filtering Products "uphill", i.e. from the many to the 1 side of the relationship. How is this possible? What is going on here? I've read over and over again that filters only flow from the 1-side to the many-side.
Thanks a lot
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.
9 Replies
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.
