Matrix Visual Limitations on Individual row expansion Direct Query

[KohliG]

The visual doesnt send the filters applied at visual/page level in Direct Query mode which results in a table scan and causing user experience issues.

Is there a way matrix visual always use filters applied at visual or page level in direct query mode.

Thanks,

Gaurav

[v-chuncz-msft]

@KohliG 

That seems to work fine for me. You may try the latest version to recreate the report.

[KohliG]

@v-chuncz-msftI'm using August 2020 version. Should I be using latest?

[KohliG]

Hi - i'm using Power BI Spark connector to connect to a Databricks cluster and have created a report using Matrix visual in Direct Query mode. In order to fetch the data and paint the visual Power BI fires many queries depending on where one is at the matrix visual.

For example I'm drilling down on one of the rows for one of the columns in the visual.  So to get all the data it fires a query which has the page and report filter appended, however in addition to this it also fires one query which looks like below.

SELECT 1 AS C_4331 FROM table WHERE (table.column = 'Some Value') LIMIT 1

Now as you can see this Query does not contain the filters applied at page or visual level and hence causes a massive scan.

Is there a way in power BI to stop it firing these kind of queries.

Much appreciate your help in advance.

Thanks