Direct Query to Azure Databricks Hive Tables not folding

[Anonymous]

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(import) 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.
so the query was something like this:

sqlQuery = "SELECT * FROMdb.table " &
        "WHERE DepartureDateSID = '" &Departure Date&"'"&
        " AND RouteSID = '" &Route SID&"'"&
        " AND ClientSID = '" &Client SID&"'"&
        Limit 1",

but the question then would be how do I define these parameters dynamically so they get their data from the parameters in a seperate paginated report?(lets say with the same parameter names)

Here is a simplified part of the DB relationships which is relevant to what I want to achieve:

Here is what I got from the performance analyzer when I tried just adding the columns from the dim tables and then the column from the Fact table within my PBI dataset:

Any help would be appreciated as how to handle this issue.

[amitchandak]

@Anonymous , have you checked Dynamic M parameters

https://blog.crossjoin.co.uk/category/dynamic-m-parameters/

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[Anonymous]

Hi @amitchandak, Yes I have tried Dynamic M queries and it is all fine if I had the parameters inside my Powerbi Desktop, but the parameters are in a seperate Paginated report. How would I get the value from those parameters from a seperated paginated report dynamically into my dataset is the problem I had with that approach.