How Do I: Add a Drill-Through to See Records in Power BI Desktop?

Imagine you have created an amazing Power BI report that summarizes information using different visuals. Power BI Desktop lets you build advanced queries, models, and reports that visualize data and the idea is that the individual records that come from your data source are aggregated and summarized in your report, giving you the possibility to see the big picture.

Let’s look at an example, where we import information from the GL Entries table in Business Central:

Here I created a matrix that displays general ledger amounts by account category, subcategory and name. And as you expect you can see the totals and subtotals.

Now a user asks you, can I also see the underlying records that make up a certain total or subtotal?

For example, in the above screenshot, where does the 14.268,40 come from?

Well, in many cases the answer is very simple. Simply right-click the field and select See Records:

When you select See Records for a data element, the Power BI Desktop canvas displays all the data associated with the selected element, and that’s really cool. But there are some limitations:

• You can’t change the data in the See Records view and save it back to the report.
• You can’t use See Records when your visual uses a calculated measure.
• You can’t use See Records when you are connected to a live multidimensional (MD) model.

See Records support only the following visualization types:

• Bar chart
• Column chart
• Donut chart
• Filled map
• Funnel
• Map
• Pie chart
• Treemap

For example if you would have used a Table instead of a Matrix visual, See Records would not be available:

Instead you can use the Show data. See Data shows you the data underlying a visualization. But the See Data option is not as detailed as the See Records. So my recommendation is to always use Matrix controls instead of Tables.

Fyi, more information is also available here:

• https://docs.microsoft.com/en-us/power-bi/desktop-see-data-see-records

But every now and then you might get a followup question: Can I limit or determine which columns are shown in the See Records or Show Data pane?

Well, to my knowledge you can’t. But there’s a workaround. The workaround consists of building your own drill-through.

1. Start by adding a new page to the report.
2. On that new page add a Table (or any other visual).
3. Then in the new page, in the visuals, add the columns yo want to see.
4. Now, in the page properties, in the Drillthrough pane, add for example the Account Name and/or Account Number:

What happens next is when you now open the previous page again, where you have the table,that has no See Records function, there’s now a Drill-through option, that links to your new page:

And on the page that contains the Matrix you now have both options:

More information about drill-through in Power BI Desktop is available here:

• https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

You can even add buttons and transfer filters… And there also a possibility to implement cross-report drillthrough:

• https://docs.microsoft.com/en-us/power-bi/desktop-cross-report-drill-through

How cool is that!