0845 643 64 63

Drillthrough

Power BI Drill Through using Multiple Data Points

A drill through in Power BI allows the reader to see secondary data related to the original page with the context of a specific data point applied, for example, drilling through on sales data can display the demographic information of the relevant customers for those sales.

One limitation of the drill through functionality is that it only allows users to drill through on a single data point. If more than one is selected, the drill through function will be disabled. Using the above example, this means that a reader can drill through to the demographic of the sales of one product at a time, but not a combination of two or three.

You can see this when using a drill through button, the button only works when one data point is selected.

Single data point selected - Button active

If you select multiple points the button is greyed out and if you hover over the it, you get the following tool tip appear:

Multiple data points selected - Button greyed out

“To drill through to [page name], select a single data point from [page name]

Curiously, since native drill throughs on card visualisations were introduced back in September 2020, Power BI considers a card to be a single data point, regardless of the number of filters applied to it.

If you drill through on the card with multiple data points selected, the drill through page will have all of the relevant filters applied.

Select multiple data points and right click the card to drill through
Filters showing both selected data points have been applied

Currently there is no method of getting the button to function with multiple data points selected, even though the above behaviour suggests there is scope to do so. At the time of writing, Microsoft have confirmed that this behaviour is intended functionality for the drill through button.

So to conclude, if you need to allow drill throughs for a multi-select scenario, currently your only option at the moment is to replace Buttons for Cards and perhaps include a tip for the reader to know its there, hopefully this may change in the future.

Debug MDX queries using Drillthrough in SSMS

One of the great features of using Excel to browse an SSAS OLAP cube is the drillthrough ability. If you double click on any cell of an OLAP pivot table, Excel will create a new worksheet containing the top 1000 fact records that went to make up the figure in the selected cell.

N.B. The limit of 1000 rows can be altered, as per one of my previous blog posts here.

This feature is pretty well known, but not many folk realise how easy it is to reproduce this in SQL Server Management Studio (SSMS). All you need to do is prefix your query with DRILLTHROUGH.

i.e. Assuming an MDX query of

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE [Date].[January 1, 2004]

Which returns the following results…

A query of

DRILLTHROUGH
SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE [Date].[January 1, 2004]

Returns the records contributing to the total figure. Great for diagnosing problems with an MDX query.

By default, only the first 10,000 rows are returned, but you can override this using MAXROWS

DRILLTHROUGH MAXROWS 500
SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE [Date].[January 1, 2004]

The columns that are returned are those defined in the Actions tab of the Cube Designer in BIDS (The Business Intelligence Development Studio).

If no action is defined, then the fact measures will be returned along with the keys that link to each relevant dimension, which tend not to be that helpful.

You can override the returned columns by using the RETURN clause

DRILLTHROUGH SELECT [Measures].[Internet Sales Amount] ON 0 FROM [Adventure Works] WHERE [Date].[January 1, 2004] RETURN [$Internet Sales Order Details].[Internet Sales Order] ,[$Sales Territory].[Sales Territory Region] ,NAME([$Product].[Product]) ,KEY([$Product].[Product]) ,UNIQUENAME([$Product].[Product]) ,[Internet Sales].[Internet Sales Amount] ,[Internet Sales].[Internet Order Quantity]


Note that there are some restrictions on what you can drill through

  • You can’t drill through an expression/calculation, only a raw measure
  • The MDX query needs to return a single cell (otherwise the cube does not know which one to drill through)
  • The data returned will be at the lowest granularity of the cube’s fact table

To explain the last point further, the cube does not return the raw data from the underlying data warehouse, but a summary of the facts grouped by unique combination of the relevant dimensions. i.e. if a warehouse table containing individual sales (by date, product, customer & store) is brought into a cube as a fact table that only has relationships with the date and product dimensions, then the cube drill through will return unique combinations of date and product, summarising sales for each combination. Extra granularity which the warehouse may contain (customer and store) will not be available.

Note that if you specify the RETURN columns, the rows are still returned at the lowest level of the fact table granularity, even if not all of the dimensions are brought out as columns. This may result in returning multiple identical records. Don’t worry, these will be distinct facts, just differentiated by a dimension/attribute that isn’t being returned.

You can find out more on TechNet here

Frog-Blog Out

Excel Cube Pivot drillthrough limited to 1000 rows

When browsing a cube using Excel 2007, you can drillthrough the measures to display up to 1000 rows of the transaction level source data.

I often get asked whether this limit of 1000 rows is configurable – well the good news is yes it is.

There is an option in the actions tab of the BIDS cube designer which allows you to specify the maximum rows, but helpfully this is ignored by Excel. Instead, you have to set it in Excel when you create a pivot.

Just click “Options” on the “PivotTable Tools” ribon, then in the “Change Data Source” dropdown click on “Connection Properties“. In this screen, just change the “Maximum number of records to retrieve” property.

Excel 2007 Pivot Options

Open SSRS report in a new Window

There isn’t yet a built-in facility within Reporting Services to open a report link (i.e. a drillthrough report) in a new browser window or tab, however it isn’t too tricky to get this to work using a bit of javascript.

javascript:void(window.open(Destination,’_blank’))

My preferred method is to wrap this up into a custom code function such as

Function OpenURL(ByVal URL As String) As String
Return “javascript:void(window.open(‘” & URL & “‘,’_blank’))”
End Function

In your report, you can then just set the navigation property to jump to a URL of a website:

=Code.OpenURL(“https://www.purplefrogsystems.com“)

Or to the path of a the drillthrough report:

=Code.OpenURL(“http://[Your Server]/ReportServer?/Folder/ReportName&rs:Command=Render”)

If you have parameters that you need to pass in, you can add these into the URL either fixed or dynamic:

=Code.OpenURL(“http://[Your Server]/ReportServer?/Folder/ReportName&rs:Command=Render &MyFirstParam=Value1&MySecondParam=” & Fields!MyField.Value)

Please note that this will not work in the BIDS development environment, it will only work once you have deployed the report to the Report Server.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon