Excel Export Underlying Data

[PIESSEQUADRO2]

Hello,

I’m experiencing issues with the Power BI Excel export underlying data functionality: it gives me the error message “Sorry we couldn’t export your visual data right now. Please try again later” when the matrix has “lots of rows” (13103) AND a particular calculated measure AND a field from a dimension.

Here the simple data model: customer dimension + fact table + date dimension

Where the ORD measure uses the inactive relationship (Fact.[ORD Date] = DateDimension.[Date]):

ORD = CALCULATE((SUM(Fact[FAT]));USERELATIONSHIP(Fact[ORD Date];DateDimension[Date]))

Here the other two measures

ORD_PrevYear = CALCULATE([ORD];PARALLELPERIOD('DateDimension'[Date];-12;month))

DIFF VS = IF(ISBLANK([ORD_PrevYear]) || [ORD_PrevYear] = 0; DIVIDE([ORD] ;[ORD]); IF([ORD]<0; -1; IF([ORD] > 2*[ORD_PrevYear]; 1; DIVIDE([ORD] - [ORD_PrevYear];[ORD_PrevYear]))))

When I try the Excel export underlying data with a matrix showing:

Customer Name + ORD measure + DIFF VS Measure

It is not working.

Instead, when I try one of these simplification, the excel export works fine:

• Select 5 top rows, i.e. reducing the number of rows
• Removing the DIFF VS Measure
• Removing the field Customer Name

It seems very strange to me. Anyone knows what I’m missing?

[v-lili6-msft]

hi, @PIESSEQUADRO2 

Please check these limitations

Limitations and considerations

• The maximum number of rows that can be exported from Power BI Desktop and Power BI service to .csv is 30,000.

• The maximum number of rows that can be exported to .xlsx is 150,000.

• Export using Underlying data will not work if the data source is an Analysis Services live connection and the version is older than 2016 and the tables in the model do not have a unique key.

• Export using Underlying data will not work if the Show items with no data option is enabled for the visualization being exported.
  ......

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data

and also if this post could help you

https://community.powerbi.com/t5/Issues/Error-Exporting-Data-to-xlsx/idi-p/444559

Best Regards,

Lin

[PIESSEQUADRO2]

Hello,

thanks for the reply: I checked every point, but no one is the case of my example.

I managed to export all the underlying data by adding a TOP filter in the visualization: the top filter is set so high (700) that all the rows are considered:

• 208 KB is the dimension of the Excel Export
• 13 103 is the number of exported rows

Still, it is not clear why the Excel Export of underlying data is not working without this trick.

However, when I try to replicate the Top Filter trick in a more complex model, the Excel Export is not working.

[v-lili6-msft]

hi, @PIESSEQUADRO2 

I think if you don't filter the visual, the underlying data that are exported will exceed the maximum rows limit because of complex model.  If you export summarized data, does it work well?

For example:

For the same visual, when I export the summarized data, it has 72rows data, but when I export the underlying data, it has 2091rows data.

Best Regards,

Lin