Export underlying data is not working in direct query

[Anonymous]

Tabular Model which I connected to PowerBI in direct query mode. Before publish, I have changed setting to export underlying data along with summarized data. Where export to summarized data is working fine.

But, When I try to export underlying data which contains 34K+ plus records are not working. Getting error as "Error exporting data" reason mentioned as "sorry, we couldn't export your visual right now. please try again later."

I have used table visual where dragged 3 columns from fact, 1 column from dimension and 1 measure( just sum of column value). And I considered all the limitations where Microsoft called out

Size of the data is less than 5 MB I suspect. it contains 34K+ plus records only. It should work in PBI service. I have tried all the options which suggested in forums, no luck. Please help here

Thanks in advance!!

[v-alq-msft]

HI, @Anonymous 

I wonder if  the tables in the model don't have a unique key and what the size of the uncompressed data is. Have you checked the option 'Show items with no data' ? Here are some limitations for exporting underlying data.

Export using Underlying data won't work if:

• the version is older than 2016.

• the tables in the model don't have a unique key.

• an administrator or report designer has disabled this feature.

Export using Underlying data won't work if you enable the Show items with no data option for the visualization Power BI is exporting.

When using DirectQuery, the maximum amount of data that Power BI can export is 16-MB uncompressed data. An unintended result may be that you export less than the maximum number of rows. This is likely if:

• There are many columns.

• There's data that is difficult to compress.

• Other factors are at play that increase file size and decrease the number of rows Power BI can export.

For further information, please refer to the official document .

Best Regards

Allan

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

[Anonymous]

Hi @v-alq-msft ,

Thanks for your response!!

What I still not understood, if it is due to size limit or compress data issue or anything. Users would except exact errors message to optimize anything right?

If it shows try sometime later, then it not make sense of accepting the limitations constraints.

As I mentioned in original post, my table has total 5 columns. Out of which 3 columns from fact table(transactional data) and 1 dimension column and 1 measure (Sum of qunatity). It holds total 34K records. And checked in database and size is less than 10MB and that is structured data

Note: On fact table, implemented cluster column store index to improve query performance. So, it is not allowed to implement primary key constraint. However, table has unique key column but not considered in that visual

Please check and let us know, if any information required to debug further

[nbird86]

Any resolution to this? I am having the exact issue. 

[Clinical_Epi]

Hi,

We encountered this issue today too, well our end users did (we use PBi Report Server), but even though we are adminsitrators we found that we were getting the same error.

I found was that some pages on our dashboard had 4-5+ tables linked into one vizual!, cutting them back to 3 tables at most was the only way I could fix this issue for us. Worked fine on all visuals, pages etc...

I think the reason this happened in this case (for us) was the tech who developed it didn't consolidate the DAX measures which resulted in lots of tables in use at once on one visual.... anyway I think it's good practice to keep your measures in one or 2 tables at most, this way one wont "come a cropper" 🙂

Best of luck

Andy @

Clin Epi