I believe Tableau and PowerPivot function differently and have different advantages. From my understanding in order to work with 40 million rows of data in PowerPivot, you need lots of memory, and in your case you have 36GB, so it is no issues for you to work with it all in-memory. I believe Tableau will only load into memory what it needs to answer the query, many times very little because the Tableau Data Engine is a columnar-style data store, so you can have only 3GB of memory and query billions of rows in Tableau. When you want to display millions of marks at once in Tableau, then you will need extra memory, but generally Tableau is used to aggregate data, so the number of marks is generally much lower.
I can't say for sure without seeing your workbook, but your quick filter may not be built as efficiently as possible. In Tableau there are many ways to accomplish tasks, and and some are slower than others. It could be as simple as optimizing the extract (adding the results of database side calculations into the data extract, so you gain the columnar data store performance gains). In my opinion, many of the speed gains and data size capabilities come because the Tableau Data Engine is a columnar style data store.
Can you provide more details on what you are doing your worksheet? or a screenshot of the entire Tableau window, with the formulas of any calculated fields in use?
I'm actually don't doing any special calculation in my worksheet more than adding a dimension, measure and a quick filter, see screenshot. I can't find any other way to do the filtering in tableau so can you please elaborate on what you mean with quick filter may not be built as efficient as possible.
When it comes to PowerPivot I can't find that powerpivot are allocating large amount of RAM.
capture.png 61.5 KB
I would recommend contacting Tableau Support, as I cannot reproduce the situation you describe, and they may be able to look at your log files to see exactly what is causing the unusual slowness.
Is the field "WaterfallPosition" a calculated field? Have you tried optimizing the extract: from the menu, Date -> Extract -> Optimize
I have a dataset with 40 million rows as a Tableau Data Engine extract, and when I create a layout similar to what you have, and I get instantaneous results when changing the filter.