Think about this case:
We have now two Experiences:
- One displaying information for On-line gross sales over time, and in comparison with the earlier yr
- One displaying information for Retail gross sales over time, and in comparison with the earlier yr
We have now 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.
Making ready the Information Mannequin
Step one is to retailer details about the at present energetic web page earlier than performing the drill-through motion.
Sadly, DAX has no perform for getting the identify of the present web page.
Due to this fact, 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” function.
There I create this desk:
Right here, I have to enter all of the report pages, which may be an originating web page for a drill-through motion.
Notice that the names entered within the desk will probably be displayed on the drill-through web page. Due to this fact, 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 identify:
We do that for each pages with the corresponding web page identify.
On the drill-through web page, we set the “Preserve 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 strategy.
That is what it appears like on the drill-through web page:
However I had conditions the place this setting induced 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 situation, however it could occur.
Nonetheless, including the column PageName as a drill-through column gained’t assist until the supply visible additionally contains this column. Including this column to a Visible is unnecessary, because it has no connection or that means with the info displayed.
Due to this fact, that is the one significant option to cross the supply web page identify to the drill-through web page.
I’m unaware of one other strategy to resolve 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 appears like this:
Based mostly on this consequence, 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
This can be a simple 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 keep in mind so as to add the identify of the brand new web page to the desk “Report Pages”.
- This is not going to work once you plan to have a second degree of drill-through pages.
For the second challenge, as quickly as you propose to leap to a different drill-through web page from the primary, you need to add additional performance.
For instance, a separate desk or one other column within the “Report Pages” desk.
It’s essential to choose the strategy relying on whether or not you propose so as to add one thing like a breadcrumb textual content, which permits the consumer to see the complete 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 identify “Particulars Web page”.
Then, you add the web page filter to the drill-through web page, specifying the drill-through web page identify.
The consequence after the primary drill-through motion will probably 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 we’ve got 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.
Because of this, we can’t use CONCATENATEX() to retrieve each values.
Due to this fact, 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 strategy with a further column:
The column SubPageName accommodates the identify 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 consequence:
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-By mavigation from: " & [Source Page] & " - " & [Source Sub Page]Conclusion
Now that I’ve supplied you with all the mandatory instruments, you possibly can construct your answer to trace the originating web page names in a drill-through situation.
It’s simple to appreciate. Nonetheless, you need to pay attention to the technical necessities and potential unwanted effects.
Upon getting mastered them, you possibly can apply them to all stories.
I want you quite a lot 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 may be freely used below the MIT License, as described in this document. I modified the dataset to shift the info to up to date dates.
