Drilldown 1000 row limit in PowerPivot or PowerBI.com Analyse in Excel

Anything related to PowerPivot and DAX Formuale
Eamonn
Posts: 3
Joined: Tue Feb 12, 2019 4:39 am

Drilldown 1000 row limit in PowerPivot or PowerBI.com Analyse in Excel

Postby Eamonn » Tue Feb 12, 2019 4:53 am

In a normal Excel pivot table when you double click on a cell you can see the build out of that number i.e. the underlying numbers/transactions making up the summary figure, which sometimes might be many thousand rows of data. However in PowerPivot or PowerBI.com Analyse in Excel the number of rows is limited to 1000 rows. Does anyone know how to override that 1000 number

It might be easier and more clear if I show you it rather than try to explain it

http://www.youtube.com/watch?v=_Wqm9KNjy54

Thanks for any insights

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Drilldown 1000 row limit in PowerPivot or PowerBI.com Analyse in Excel

Postby PhilC » Tue Feb 12, 2019 12:27 pm

Does this work?


https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/19661551-power-pivot-allow-drill-down-to-more-than-1000-row

Actually you can go well beyond the 1k limit. As a matter of fact you can go to 1,048,576 rows. The trick is
1) Place your mouse OUTSIDE of a pivot table
2) Click the DATA menu
3) Click Connections
4) In the Workbook Connections dialogue box click "ThisWorkbookDataModel" (at least that's what mine says and for me it was the last connection right at the bottom )
5) Click Properties. In the Usage tab, “Maximum number of records to retrieve” should not be grey out and you can enter the number of rows that you wish to retrieve.

Eamonn
Posts: 3
Joined: Tue Feb 12, 2019 4:39 am

Re: Drilldown 1000 row limit in PowerPivot or PowerBI.com Analyse in Excel

Postby Eamonn » Tue Feb 12, 2019 1:01 pm

Thanks Phil already tried it however that part is greyed out and therefore you cannot change the olap number. Or at least I haven't been able to. You'll see at about a minute 20 seconds of the video I uploaded me trying to do the same however the area is all grayed out.

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Drilldown 1000 row limit in PowerPivot or PowerBI.com Analyse in Excel

Postby PhilC » Tue Feb 12, 2019 1:39 pm

Apologies, I did not look at the video. Sorry.

Eamonn
Posts: 3
Joined: Tue Feb 12, 2019 4:39 am

Re: Drilldown 1000 row limit in PowerPivot or PowerBI.com Analyse in Excel

Postby Eamonn » Tue Feb 12, 2019 10:06 pm

no worries Phil, very much appreciate the input!


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron