Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlB
Super User
Super User

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

View solution in original post

9 REPLIES 9
Zubair_Muhammad
Community Champion
Community Champion

@AlB

 

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/

 

 

 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

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

 

   

Anonymous
Not applicable

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 Smiley Happy

Thanks so much for pointing me in the right direction. This had been bothering me for some time now. 

 

Anonymous
Not applicable

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:

 

https://www.sqlbi.com/articles/expanded-tables-in-dax/

@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.

@joepath

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.

https://community.powerbi.com/t5/Desktop/Powerbi-DAX-measure-showing-different-result-for-Card-and/m...

 

Thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.