Direct Query Distinct Count Big Data

[MattKarriker2]

Good day forum

I am having an issue returning a distinct count of customer for a large data model due to the the 1M row data limit for direct query to Azure Synapse.  The issue we have is there are about 57M customer records.  The query sent to Synapse returns a distinct list of Customer keys filtered according to any filters being applied.  If we look at customers for 1 store for 1 day I have no issues with the distinct count measure as the row count is less than 1M.  However for all stores for one day we receive the error as the row counts for the use case is 1.6M rows.  

Can anyone share any tips or tricks that will affect the query being sent to the database that will perform the distinct count aggregation on the database side rather than returning the distinct list of values back to Power BI to perform the aggregations.  

[Anonymous]

Hi @MattKarriker2 

Cloud source has one-million-row limit in Power BI. If your report is not in Premium capacity, the result your query return has one-million-row limit as well, or it will show error. 

For reference: Limitations of DirectQuery

• There's a one-million-row limit for cloud sources, with on-premises sources limited to a defined payload of about 4 MB per row (depending on proprietary compression algorithm) or 16MB data size for the entire visual. Certain limits may be raised when using Premium capacity. The limit doesn't affect aggregations or calculations used to create the dataset returned using DirectQuery. It only affects the rows returned. Premium capacities can set maximum row limits, as described in this post.

  For example, you can aggregate 10 million rows with your query that runs on the data source. The query accurately returns the results of that aggregation to Power BI using DirectQuery if the returned Power BI data is less than 1 million rows. If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit).

Here I suggest you to add some filter in your query, to keep the result your query retuen less than 1M.

Or you can put your report and dataset in Premium capacity workspace.  Capacity admin can set the Row limit in admin portal in Power BI Service. 

For reference: Datasets

Max Intermediate Row Set Count

The maximum number of intermediate rows returned by DirectQuery. The default value is 1000000, and the allowable range is between 100000 and 2147483646. Note that the upper limit may need to be further constrained based on what the datasource supports.

Here is a post with similar issue like yours hope it could help you.

For reference: Direct Query 1 million row limitation in Power BI Premium workspace

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MattKarriker2]

Thanks @Anonymous  for your response.  This dataset is in Premium Capacity GEN2.  GEN2 is not currently configurable for Intermediate Row Count.  The default of 1M is all that is allowed.  

I am looking for Tips or Trick to adjust the back end query sent to the database.  Currently the query produced from Power BI selects a list of Customer Keys in one query to pass back to PBI to perform the distinct count. I feel this is because the attribute we want to count is not on the fact table itself.  I am looking for a way to force the backend query to "select Count_Big(Distinct FieldName)" rather than "Select FieldName ... Group by FieldName" and passing this back for futher counting.  

Is there any DAX pattern known to change the backend query in such a manner?