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