Forum Discussion
Pivote columns refresh error
Buffering doesn't affect folding. It affects speed. It gathers all of the items in the list at once, then sends the buffered list to the SQL server in the IN clause.
If you don't buffer, it streams the data into the IN clause and takes significantly longer.
As a result, folding or not, I always use a buffered list for List.Contains. And yup, Anonymous taught that trick to me.
Yessir, it sends the list as an IN clause, like
Customer ID IN (A12345, A42956, A23584, A65485, A46584).
--Nate
Just tested this and sharing findings for future generations of PBIers:
After much confusion, realised the arguments were the wrong way round in the List.Contains function. It should be:
List.Contains(filterValuesTable[columnWithFilterValues], [columnToActuallyFilter])
Also discovered that the list doesn't need to be buffered to preserve folding - PBI is smart enough to generate the IN clause either way:
However, recommendation to buffer the list for performance gains is very valid.
Pete
Darnit! I still get those reversed sometimes! Yes, the FilterValuesList first, then the TableColumnToFilter second.
I'm usually never at the PC when I write these comments...
But yeah, using a buffered list in a List.Contains Table filter makes filtering extremely fast. And the fact that it preserves query folding is also pretty seriously awesome. It can handle a few thousand values, you just have to make sure that your resulting SQL statement doesn't exceed the query character limit, which I can't remember.
edhans has an article about using List.Contains on his site; I'd already been using it for a while, and I casually made a comment on one of Ed's posts on Twitter about buffereing the list. He, being an actual professional, tried it, measured the performance gains, and added an addendum to the article here https://www.ehansalytics.com/blog/2020/5/20/using-listcontains-to-filter-dimension-tables
His article is much more thorough than the stuff I post on here!
--Nate
BA_Pete Anonymous Sorry guys to bother you once again, but I've made a mistake in the first decsirption, and in fact, I'm using SSAS as my data source. As from what I've read, SSAS support MDX/DAX. Therefore, my Native Query is grayed out, is there a possibility, that still, the way I was filtering data, could be the reason why I can't load my data correctly? Thanks!
Buffering doesn't affect folding. It affects speed. It gathers all of the items in the list at once, then sends the buffered list to the SQL server in the IN clause.
If you don't buffer, it streams the data into the IN clause and takes significantly longer.
As a result, folding or not, I always use a buffered list for List.Contains. And yup, Anonymous taught that trick to me.