Dynamic measures and Drill Through!

[Mark]

Hi all,

In our Stock table, we have 2 different Ageing values, we’ll call them X Days and Y Days. We then group these into ageing bands: 0 - 30, 31 - 60, 61 - 90, 91 - 120 and 121+.

The business is interested in both X Days ageing and Y Days ageing and want to see a bar chart showing the number of units in each category. However we only want to display one chart and let the user toggle between analyzing X Days and Y Days. The user then needs the ability to drill down to a Detail report to see the stock units in each age band.

Please see attached which I have created to demonstrate my problem, along with the 2 attempts I’ve made.

Stock by Age Band.pbix (127.5 KB)

Standard
This shows 2 bar charts, simply reporting over the Age Bands columns that I added to my Stock fact table. The first groups by X Days and the second by Y Days. The users can drill through to Stock Detail and the numbers are correct, however the sort of the age bands is incorrect:

I suppose I could create a bookmark to toggle between these two charts and create 2 connected tables to handle the sorting but it wouldn’t be very dynamic and as much as possible I prefer to try and do things in DAX rather than end of with lots of bookmarks!

I would also prefer a solution which would allow for the removal of the Age Band columns from the fact table.

Dynamic Attempt 1

I thought I would have a go at dynamically grouping and segmenting the data using Sam’s example in this video

I created a new table Day Option with the values ‘X Days’ and ‘Y Days’ and created a slicer to allow the user to toggle between both.

I created another table, Age Band Groups which would dynamically group the units into an Age Band , based on the age landing between the From and To values:

I then created measure [Stock Units (By Age Band)] and depending on the selected value of the slicer, calculated the number of units in each group based on From and To:

I then created a new bar chart, based on the Age Band from this disconnected table and added my toggle and the functionality worked great…

HOWEVER the drill through to Stock Detail did not work and returned all units

Related Table Attempt 2

So I figured that for a drill though to work we need a physical relationship between my Age Band table and my Stock table. So I created a duplicate of the table Age Band Group and this time connected it to my Stock fact table - twice. Once on X Age Band and then again on Y Age Band:

I then created new measures [Stock Units (By Age Band X Days)] and [Stock Units (By Age Band Y Days)] which used USERELATIONSHIP to manage the joins.

I then created a final measure [Stock Units (By Age Band v2)], which returned on or the other based on my slicer option.

This approach worked fine in the chart and replicated the functionality of Attempt 1, but again the drill through was the stumbling point and it only worked on X Days, but not Y Days.

SO!

I am hoping someone can help suggest a way that I can toggle between the two charts, have the X Axis correctly sorted and most importantly have the Drill Through work. As said, I would prefer to avoid bookmarks, but if that it is the only option then so be it!

Many thanks as always,

Mark

[EnterpriseDNA]

Thanks for posting your question @Mark To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

[Rajesh]

Hi @Mark

I did some changes to your data model.

Stock by Age Band.pbix (117.6 KB)

[Mark]

Hi @Rajesh

Thank you for taking the time to look at this for me and for the suggested solution. However, I don’t think it would be viable in the real world as you have had to duplicate every record in the fact table, doubling the size of the table. Works ok in this example with 600 rows, but this fact table could contain many millions of records with many other measures.

The only other solution I have come up with is to resort to bookmarks and have two Dim tables, one joining to X Age Band and the other joining to Y Age Band and then toggle between them. See attached:

Stock by Age Band v3.pbix (184.3 KB)

Not as elegant as a Dax solution, but perhaps that is just not feasible in this scenario?

Thanks again for the input though

Mark

[Heather]

@Mark - unless I’ve completely missed something in your first message, I was able to get your First Attempt to work, by just re-tooling DrillThrough page (replaced Stock Units with Stock Units (by Age Band)).

See attached for that part of the solution.
NOTE: I have not done anything with your DAX code, and this doesn’t fix the request to remove the banding from the Fact table.
Stock by Age Band (1).pbix (127.8 KB)

[Mark]

Hi @Heather

At first I was really stumped as your solution did work, yet when tried to replicate it in my original solution it did not! But then I spotted that you had applied a filter on the drill through table to exclude where blank:

Whilst this works fine in this scenario, would it not prevent other Drill Throughs from other visuals from working if they were to drill through on a non related dimension? Eg Stock Type? Ultimately we want to have a few visuals which summarise Stock, each offering the ability to drill down to Stock Detail.

Many thanks

Mark

[sam.mckay]

Well done Heather on this one

[Heather]

Ah, I see what you are meaning - can you add some of these other issues into your sample, I can see if it’s possible to build out some type of aggregated value to limit the drillthrough table to.

My solution, as you may have seen, is only going to let you drill-through to this page from a visual with the “Stock Units (by Age Band)” measure included. It may be necessary to have a different drill-through page for other types of analysis.

The good news is, you can hide drill-through pages from the tab list if needed, so you could effectively build two nearly identical drill-through pages, but change the Stock Units (by Age Band) measure out for your standard Stock Units, for use in cases where you need to drill from the more standard measure.