I am trying to use direct query on a Very large table (tens of billions of rows) that pulls data from hive tables on Azure Databricks which points to ADLS Gen2(delta files). The issue is that for whatever reason query folding is disabled even on Source, so it just tries to pull all data before applying filters and obviously it cannot(takes too long and timesout).
I try filtering it using my other 3 Dimension tables and when I use Dax Query builder to recreated the filters that are used in my paginated report, after an hour of loading it just returns the entire column from my Direct Query Fact table(instead of just one row of record) so filtering is not going through this Direct Query for whatever reason.
I don't have the option of pulling all the data into Powerbi since it would end up being close to 400Gb and we don't want to do that since the data is going to grow.
I tried doing some sort of Native Query with the parameters being dynamically chosen by the user, but since this dataset is being used by a Paginated report I don't know how to map my parameters to what the user is going to choose in the paginated report.
Any help would be appreciated as how to handle this issue.