Report is failing in PowerBI.com

[AnujMishra_09]

The following error is showing when opening the report in PowerBI.com and when I select a slicer it starts loading.

Please let me know if it is a memory or configuration issue, how can we fix it?

[AnujMishra_09 · Accepted solution]

Please see the DAX below which is causing issue:

CALCULATE(DISTINCTCOUNT('AR trans measures'[Sales invoice key]),FILTER('AR trans measures','AR trans measures'[Sales invoice key]<>-1 && 'AR trans measures'[Payments] <> 0 && 'AR trans measures'[Open receivables] = 0))

[v-yuta-msft]

@AnujMishra_09,

As the error message says, your visual has attempted to query too much data for us to complete the result with the available resources. This means your data model is too complicated for power bi service server to implement. You may improve the data model and DAX function(e.g.: reduce the table scanning). 

For more details about how to improve the data model and DAX formula, you can refer to link below:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

http://radacad.com/optimising-power-bi-data-models

https://support.office.com/en-us/article/create-a-memory-efficient-data-model-using-excel-and-the-po...

Community Support Team _ Jimmy Tao

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

[AnujMishra_09 · Accepted solution]

Please see the DAX below which is causing issue:

CALCULATE(DISTINCTCOUNT('AR trans measures'[Sales invoice key]),FILTER('AR trans measures','AR trans measures'[Sales invoice key]<>-1 && 'AR trans measures'[Payments] <> 0 && 'AR trans measures'[Open receivables] = 0))

[v-yuta-msft]

@AnujMishra_09,

The measure seems normal, how many rows and columns are there in your report? Please also refer to the limitation in power bi service.

• Dataset size limit - there is a 1 GB limit for each dataset in the Power BI service.
• Row limit - the maximum number of rows in your dataset (when not using DirectQuery) is 2 billion, with three of those rows reserved (resulting in a usable maximum of 1,999,999,997 rows); the maximum number of rows when using DirectQuery is 1 million rows.
• Column limit - the maximum number of columns allowed in a dataset, across all tables in the dataset, is 16,000 columns. This applies to the Power BI service and to datasets used in Power BI Desktop. Power BI uses an internal row number column per table included in the dataset, which means the maximum number of columns is 16,000 minus one for each table used in the dataset.

Community Support Team _ Jimmy Tao

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

[AnujMishra_09]

Row count is 10K to 45 K and there are not so many columns used in visual. I could not understand the meaning of 16000 columns.

In this case DISTINCTCOUNT is the culprit.

[AnujMishra_09]

I tried DistinctCount in Calculate with one condition only and it looks fine but when I use FILTER () inside that, it again starts delaying.

[AnujMishra_09 · Accepted solution]

I tried the following

Measure1 = CALCULATE (DISTINCTCOUNT ( ), FILTER1)

Measure2 = CALCULATE (Measure1, FILTER2)

Measure3 = CALCULATE (Measure2, FILTER3)

It worked!!