Semantic Model with Direct Query to Databricks does not return all rows of the table in the Service

[GF-PBI1]

Hi,

I created a Semantic Model with Direct Query to a Databricks materialised view. I configured it in my desktop version and it worked perfectly, showing all data. However when I publish to the Service, it does not bring in all rows fom Databricks. The table visual in PBI shows zeroes for certain rows. Is there a setting in the PBI tenant that sets a limit to the number of rows returned? It is a large table with over 100 million rows

 What I am also struggling to undestand is the the card KPI which is a simple count of rows is also way to low. Surely the compute for this would be in Databricks and would only return one value so it should be correct?

Any advice would be apprecited!

[GF-PBI1]

Hello @collinq  @dmkblesser  @v-ssriganesh 

Thank you for your feedback — it helped point me in the right direction, which I really appreciate.

It looks like the issue lies with my DAX measure. I'm using the SUMX function to calculate a USD value,   SUMX(LocalValue * USDFxRate). When I use SUM(LocalValue) measure, all rows return as expected. However, when I multiply by USDFxRate, some rows return zero.

What is also confusing is that the SUMX(LocalValue * USDFxRate) measure returns all rows correctly in PBI Desktop, but not in the PBI Service. The SUM(LocalValue) measure is correct in both environments.

I  looked at the Query History in Databricks , but couldn't find the queries with SELECT and SUM. The only queries listed were simple "Select columns From xx". Is there a setting or permission that gives more detail in Databricks?

I did however manage to view the SQL generated query in DAX Studio, but this is not ideal.

Please let me know if you have any further ideas on this -  especially why there would be a difference btw running the same pbix on Desktop and in the Service.

[GF-PBI1]

Hello @collinq @dmkblesser @v-ssriganesh 

Thank you for your feedback — it helped point me in the right direction, which I really appreciate.

In the PBI Service, it looks like the issue lies with my DAX measure. I'm using the SUMX function to calculate a USD value -  SUMX(LocalValue * USDFxRate). When I use SUM(LocalValue) alone, all rows return as expected. However, when I multiply by USDFxRate, some rows return zero - there is no error or timeouts just rows in the table with Zero USD values.

This is confusing because the SUMX(LocalValue * USDFxRate) measure returns all rows correctly in PBI Desktop, but not in the PBI Service. The SUM(LocalValue) measure, is correct in both environments.

I have been trying to view the Query History in Databricks, but it does not seem to return the measure queries. It only shows queries that select columns - no SUM() etc. Is there a setting or permissions in Databricks to see more detail?
I manged to view the SQL queries generated from the DAX query in DAX Studio but this is not ideal. 

Thank you again for your help. Let me know if you have any further insights on why the SUMX is causing issues in the Service.

[v-ssriganesh]

Hi @GF-PBI1,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @dmkblesser & @collinq for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[dmkblesser]

Hey @GF-PBI1 , another thing you can try is since you are using direct query, go to databricks and check the query history. Run the same query from the history in Databricks to see what result you receive. If the result matches with what you see from Power BI, then like @collinq mentioned, there could be issues with your Power BI queries. 

[collinq · Accepted solution]

Hey @GF-PBI1 ,

I am thinking that this is Power BI and not Databricks.  Power BI has a limit of 1 million rows on Direct Query.  BUT, that is per visual, not per dataset. 

There could be some issues in the report itself though - you may have some query folding issues or just a limiter in the queries?  Also, are you seeing any timeouts (on either side - Databricks or Power BI)?

You might try to aggregate the data to help bring it in more.

Or, you could look at the performance Analyzer in Power BI or even look at Fiddler to trace the network to confirm that it is actually working as expected.