We have data (size ~100K) in On-Premise SQL Server database, and created an Power BI report in "Direct Query" mode. Observed an interesting issue while exporting data from Table visual to excel.
Has 2 visuals... "Card" , "Table"
No filters on data
No Aggregates on data
Card is showing "100K"
Issue #1: when I export data from Table to excel...it is exporting only ~60K rows. verified Power Bi SQL query at Gateway using SQL Profiler... there it is clearly showing the Row count as 100K, that has returned from database.
I tried to convert report from “Direct Query” to “Import” mode. Then excel export is working as expected. But need to why it causing issues in “Direct Query” mode.
Issue #2: One more observation is that, even though there are NO Aggregates in my report … SQL query generated by Power BI is having “Group By”, which is not necessary and will cause performance hit.
Can anyone try to explain the reason for this behaviour?