Matrix reporting different Time Periods with Drillable functionality

DAX DAX Calculations
1

Hi all,

Please see report attached: Drill Through on Time Period.pbix (116.5 KB)

I want to create a Matrix which will summarise the number of Enquiries, by Method of Contact, by different time periods:

  • Current Week
  • Last Week
  • Current Month
  • All Time

image

I can create measures for ‘Current Week’ , ‘Last Week’ etc, however when you Drill Through to a detail report, filters contained within the measures are not passed through to the drill down report.
So for example if I drill down on the 7 enquiries highlighted above for Internet/Email, the detail report will contain 155 records and not 7.

This problem can be demonstrated on tabs Summary 1 and Detail 2 in my report.

For the Drillthrough to work, I need to add a Dimension of Time Period to the columns attribute of my matrix. However as a single record in my Enquiries Fact table can be attributed to multiple time periods (eg Current Week and Current Month), I can’t simply add a Time Period column to my Fact table.

I have come up with one solution which is to create a second fact table [EnquiriesByPeriod], which is made up of appended subsets of my original Enquiries fact table, each containing rows for the desired time period and adding a new column ‘Time Period’ to identify each:

image
image480×640 15.3 KB

This now allows me to create my Matrix, using the new ‘Time Period’ column and utilising one measure for the count:
image

image

Now my Drill Through works as expected and I get 7 rows in my Detail report. :slight_smile:

See tabs Summary 2 and Detail 2 in my report.

However, I am aware that there are some draw backs of this.

  • I am creating a 2nd fact table which will contain many more records than the original.
  • I cannot relate my Date table to the second fact table as it complains of a circular dependency
  • As the users will want to see many more columns in their detail report, each Summarize statement in my derived fact table will need to contain many more fields.

So that is my attempt at fixing the problem and I would be interested to receive other peoples views on the solution or if in fact there is a better and more simply work around to the Drill Through problem!

Thanks

Mark

2

There is no need to recreate your fact table, you simply need a different drill-through page for each of the measures on your original table.

For each of the drill-through pages, I have changed the measure recognized for the drill, and put that same measure onto the table on the page:

image

image

Now, for my example, I have named the first two drill pages to match the corresponding measures - so you can easily see that it is different. But for the final two drill pages, I have them both named as Drill, so it will look like the same page is being accessed by your end-user. The way to accomplish this is to add additional spaces to the name of the tab, so the first one is named Drill(space), and the next one is named Drill(space)(space).

Also, i have hidden all four of the drill pages, I find this makes this easier for my end-users, to not have to see the page until they drill to it.

image

Drill Through on Time Period - EDNA solution.pbix (115.3 KB)

4 Likes
3

Hi @Mark, did the response/training content provided by @Heather help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

Hi @Heather

Thank you!

Once again I have obviously over-thought it. I have done many Drill Through pages before and already use the space trick, but for some reason I obviously wasn’t thinking to clearly late on a Friday night when tackling this one. I simply needed to add my specific drill through measure to my table, rather than the generic No Of Enquiries.

Thanks again,

Mark

5

happy to help - we all have those late Friday brain freezes… :smile: