Inconsistent drillthrough behavior in Power Pivot and SSAS Tabular

When you define more than one relationship between two tables, drillthroughs may return unexpected results.

Get the file!

The problem

If two or more relationships exist between two tables, knowing which relationship is active is not enough to determine the drillthrough behavior of your model.

As a consequence, if you change the active relationship in your model, you may observe unexpected drillthrough behaviors.

Consider the following diagrams, based on the same model.

This is a simple model with two tables. There are two relationships in this model, both linking the table Data to the table Dim: one using the column Rel1, the other one using the column Rel2.

We will call these relationships Rel1 and Rel2 respectively. As you can see from the diagrams, Rel1 is active in both cases.



In the first case, however, drillthrough will be based on Rel1. In the second case, it will be based on Rel2.

It appears drillthrough occurs according to the bottommost relationship in the Power Pivot diagram view (this is the other way around in Visual Studio, if I recall correctly).


Here are the data in both tables.

Click here –>
Rel1 Rel2 Comment
Click here –> Drillthrough based on Rel1
Click here –> Drillthrough based on Rel2

We can create a simple pivot table like the following.


Just after we created the relationships, double-clicking on the cell will create a new sheet returning the following results.

[$Data].[Rel2] [$Data].[Comment]
Drillthrough is based on Rel1

The Comment column makes it easy to spot which relationship was used for the drillthrough: Rel1.

This works as expected.

Changing the active relationship in the diagram view

Let us activate Rel2 in the diagram view, using the following steps:

  1. De-activate Rel1
  2. Double-click on Rel2, toggle the Active checkbox
  3. Press OK

Take a look at the result. The active relationship is now the topmost relationship in the diagram view.

Check the drillthrough sheets. Despite the update, the results stay the same.

Let us activate Rel1 again (using the same steps as before), and check the result. The drillthrough was done according to Rel2!

$Data].[Rel2] [$Data].[Comment]
Drillthrough is based on Rel2

Let us activate Rel2 again: drillthrough was done according to Rel1.

$Data].[Rel2] [$Data].[Comment]
Drillthrough is based on Rel1

You can ad lib this.

Note that if you activate a relationship using the following steps:

  1. Right-click the relationship
  2. Mark as active

Its position in the diagram view will not change.

As a corollary, it will also have no impact on the drillthrough behavior.


The easiest way to change the active relationship in a model while maintaining a consistent drillthrough behavior is to

  1. delete the relationships
  2. recreate them, starting with the (new) active one.

Slicers and Drill-through in Excel.

While I was playing around with Excel 2013, I noticed some inconsistency in the way slicers behave depending on whether they are used to filter a PowerPivot table, or whether they are used to filter a traditional pivot table.

With traditional pivot tables, slicers do not affect drill-through if they are applied on hidden fields. In other words, if you use a slicer to filter Field1, then you must place Field1 somewhere in your table (row, column, or page) in order for the drill-through to behave as expected.

This is not the case with PowerPivot tables. Drill-through works as expected.

%d bloggers like this: