In the previous blog post we discussed how to define the details of the drill through feature of a Pivot table.
Show Details – default results
As we learned how to define the results of the Show Details capability, we encounter another potential issue. As you can see in the image below the cell selected represented almost 2.000 rows, but by default you only get the first 1.000 rows. This way this feature it not that useful.
In an ideal world you would expect to see all the data and if you miss the first row you might believe you actual have it. This can cause a lot of confusion.
Fortunately, there is a solution to get all the details.
Connection Properties – OLAP Drill Through
If you go to the properties of the connection, you will see an option that most people ignored so far.
It is called Maximum number of records retrieve.
You can increase this number to anything that an excel sheet can cope with. Of course, getting more data require an efficient data model, good internet connection and a suitable hardware spec.
Show Details made more useful
Together with the Details Rows Expression and increasing number of rows retrieved in Show Details, we can increase the value of these standard Excel functions.
Considerations
- Because this setting is in Excel it cannot be set in the Power BI model.
- You need to open the file in the desktop version of Excel you cannot use or set this feature in the Excel Online.
- It is possible to create a connection file to save the details, but it does not save this setting. This will be useful in the next part of this series. For now, you need to save the excel file created from Analyze in Excel with this setting modified to a shared drive for the excel users to use.
- Currently searching for a solution to change this setting globally for all excel files so it is not a manual effort every time. If you know one, please share it with me.
Summary
This and the previous post not just ensure that the Excel users do not feel any negative impact by using a Power BI dataset but instead adds value to them. In the next blog I will show how you can reduce distractions and complexity when working with a Power BI dataset in Excel.
Hope you like this blog post, see you in the next one.
