Imagine this situation:
We have two Reports:
- One showing data for Online sales over time, and compared to the previous year
- One showing data for Retail sales over time, and compared to the previous year
We have one details page, which serves as a drill-through target from both pages, displaying detailed information about sales.
The question now is: How can we know which page was the originating page from the drill-through?
Here is how we can create a solution for a dynamic title on the drill-through page showing the originating page.
Preparing the Data Model
The first step is to store information about the currently active page before performing the drill-through action.
Unfortunately, DAX has no function for getting the name of the current page.
Therefore, we must create a custom solution.
The first step is to create a table with the names of all the pages in the report.
I do this with the “Enter Data” feature.
There I create this table:
Here, I must enter all the report pages, which can be an originating page for a drill-through action.
Note that the names entered in the table will be displayed on the drill-through page. Therefore, we enter meaningful names, which can deviate from the page names.
The next step is to create a measure to get the current value:
Source Page = SELECTEDVALUE('Report Pages'[PageName])That’s all to prepare for this solution.
Set up the Report pages
Next, we must add a filter on each of the two pages, which sets the page name:
We do this for both pages with the corresponding page name.
On the drill-through page, we set the “Keep all filters” setting to On:
This setting must be activated to ensure that the solution works as expected.
The reason for setting this to “On” is that the filter added to the source page must be passed to the drill-through page. I can see the value in DAX only with this approach.
This is what it looks like on the drill-through page:
But I had situations where this setting caused unwanted effects. For example, when the drill-through page must display data that is not affected by filters existing on the source page or source visual. This isn’t a typical scenario, but it can happen.
However, adding the column PageName as a drill-through column won’t help unless the source visual also includes this column. Adding this column to a Visual makes no sense, as it has no connection or meaning with the data displayed.
Therefore, this is the only meaningful way to pass the source page name to the drill-through page.
I’m unaware of another approach to solve this challenge.
Does it work?
Now, let’s add a card visual to the drill-through page and add the measures created before as the Value.
After a drill-through action from the Retail Sales page, it looks like this:
Based on this result, we can create a measure for a title or subtitle text, which includes this measure.
That’s all we need to do to complete the solution.
Drawbacks
This is a straightforward solution to obtain the information about the originating page.
Nevertheless, it has two drawbacks:
- As soon as I add a new report page, which can act as a drill-through source page, I must remember to add the name of the new page to the table “Report Pages”.
- This will not work when you plan to have a second level of drill-through pages.
For the second issue, as soon as you plan to jump to another drill-through page from the first, you must add further functionality.
For example, a separate table or another column in the “Report Pages” table.
You must select the approach depending on whether you plan to add something like a breadcrumb text, which allows the user to see the entire path, from the first to the second source page.
Let’s look into why this is needed:
For example, you add a new row to the “Report Pages” table for the first drill-through page with the page name “Details Page”.
Then, you add the page filter to the drill-through page, specifying the drill-through page name.
The result after the first drill-through action will be this:
- The blue-marked filter is the one that sets the PageName to “Details Page”.
- The green-marked filter is the one that passed with the drill-through action and is set to “Retail Sales”.
What we have now are two filters, which overwrite each other, resulting in a blank output. The result is not a table with both filter values, but conflicting filters.
For this reason, we cannot use CONCATENATEX() to retrieve both values.
Therefore, we require either an additional column in the report pages table, which is used in this situation, or a separate table for the detail pages.
Here is the approach with an additional column:
The column SubPageName contains the name of the drill-through page(s) reachable from the source pages.
After adding the Page Filter to the SubPageName and adding a new measure to retrieve the value of the SubPageName column, I get this result:
Combining these two measures into one title-measure can create a breadcrumb path that can be displayed on the second drill-through page:
The used measure is just a concatenation of a text and the two measures:
Sub Page Breadcrumb = "Drill-Through mavigation from: " & [Source Page] & " - " & [Source Sub Page]Conclusion
Now that I have provided you with all the necessary tools, you can build your solution to track the originating page names in a drill-through scenario.
It is straightforward to realize. However, you must be aware of the technical requirements and potential side effects.
Once you have mastered them, you can apply them to all reports.
I wish you a lot of success with it.
References
Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.
The Contoso Data can be freely used under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.
