Think about this example:
Now we have two Reviews:
- One displaying information for On-line gross sales over time, and in comparison with the earlier 12 months
- One displaying information for Retail gross sales over time, and in comparison with the earlier 12 months
Now we have one particulars web page, which serves as a drill-through goal from each pages, displaying detailed details about gross sales.
The query now could be: How can we all know which web page was the originating web page from the drill-through?
Right here is how we will create an answer for a dynamic title on the drill-through web page displaying the originating web page.
Getting ready the Information Mannequin
Step one is to retailer details about the at the moment lively web page earlier than performing the drill-through motion.
Sadly, DAX has no operate for getting the title of the present web page.
Subsequently, we should create a customized answer.
Step one is to create a desk with the names of all of the pages within the report.
I do that with the “Enter Information” characteristic.
There I create this desk:
Right here, I have to enter all of the report pages, which could be an originating web page for a drill-through motion.
Be aware that the names entered within the desk shall be displayed on the drill-through web page. Subsequently, we enter significant names, which might deviate from the web page names.
The following step is to create a measure to get the present worth:
Supply Web page = SELECTEDVALUE('Report Pages'[PageName])That’s all to arrange for this answer.
Arrange the Report pages
Subsequent, we should add a filter on every of the 2 pages, which units the web page title:
We do that for each pages with the corresponding web page title.
On the drill-through web page, we set the “Hold all filters” setting to On:
This setting should be activated to make sure that the answer works as anticipated.
The rationale for setting this to “On” is that the filter added to the supply web page should be handed to the drill-through web page. I can see the worth in DAX solely with this method.
That is what it seems to be like on the drill-through web page:
However I had conditions the place this setting brought about negative effects. For instance, when the drill-through web page should show information that isn’t affected by filters present on the supply web page or supply visible. This isn’t a typical state of affairs, however it could possibly occur.
Nevertheless, including the column PageName as a drill-through column gained’t assist except the supply visible additionally contains this column. Including this column to a Visible is senseless, because it has no connection or that means with the info displayed.
Subsequently, that is the one significant solution to move the supply web page title to the drill-through web page.
I’m unaware of one other method to unravel this problem.
Does it work?
Now, let’s add a card visible to the drill-through web page and add the measures created earlier than because the Worth.
After a drill-through motion from the Retail Gross sales web page, it seems to be like this:
Based mostly on this outcome, we will create a measure for a title or subtitle textual content, which incorporates this measure.
That’s all we have to do to finish the answer.
Drawbacks
It is a easy answer to acquire the details about the originating web page.
However, it has two drawbacks:
- As quickly as I add a brand new report web page, which might act as a drill-through supply web page, I have to bear in mind so as to add the title of the brand new web page to the desk “Report Pages”.
- This is not going to work while you plan to have a second degree of drill-through pages.
For the second subject, as quickly as you intend to leap to a different drill-through web page from the primary, you have to add additional performance.
For instance, a separate desk or one other column within the “Report Pages” desk.
You should choose the method relying on whether or not you intend so as to add one thing like a breadcrumb textual content, which permits the consumer to see the whole path, from the primary to the second supply web page.
Let’s look into why that is wanted:
For instance, you add a brand new row to the “Report Pages” desk for the primary drill-through web page with the web page title “Particulars Web page”.
Then, you add the web page filter to the drill-through web page, specifying the drill-through web page title.
The outcome after the primary drill-through motion shall be this:
- The blue-marked filter is the one which units the PageName to “Particulars Web page”.
- The green-marked filter is the one which handed with the drill-through motion and is about to “Retail Gross sales”.
What now we have now are two filters, which overwrite one another, leading to a clean output. The result’s not a desk with each filter values, however conflicting filters.
For that reason, we can’t use CONCATENATEX() to retrieve each values.
Subsequently, we require both a further column within the report pages desk, which is used on this state of affairs, or a separate desk for the element pages.
Right here is the method with a further column:
The column SubPageName incorporates the title of the drill-through web page(s) reachable from the supply pages.
After including the Web page Filter to the SubPageName and including a brand new measure to retrieve the worth of the SubPageName column, I get this outcome:
Combining these two measures into one title-measure can create a breadcrumb path that may be displayed on the second drill-through web page:
The used measure is only a concatenation of a textual content and the 2 measures:
Sub Web page Breadcrumb = "Drill-Via mavigation from: " & [Source Page] & " - " & [Source Sub Page]Conclusion
Now that I’ve offered you with all the mandatory instruments, you’ll be able to construct your answer to trace the originating web page names in a drill-through state of affairs.
It’s easy to understand. Nevertheless, you have to concentrate on the technical necessities and potential uncomfortable side effects.
Upon getting mastered them, you’ll be able to apply them to all reviews.
I want you a number of success with it.
References
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset totally free from Microsoft here.
The Contoso Information could be freely used underneath the MIT License, as described in this document. I modified the dataset to shift the info to modern dates.
