Forum Discussion
Drillthrough button with multiple visual filters
I did, the video was helpful but it did not provide the solution for the specific case. Later I'll explain my solution and then mark it so that everyone can see it.
Thanks for the input
Anonymous What specific area it didn't address, and what you still see is not working for you?
- Anonymous6 years agoNot applicable
Essentially the point is the following
With the "right-click" way of using drillthrough, if you had multiple destination pages and you wanted to reach them only from certain pages/visual you needed to duplicate the same column many times in order to avoid to drillthrough the wrong way
I'll explain myself
I have 4 pages in my report:
-Orders (has a chart that shows orders by Country and another that shows orders by Product Family)
-Deliveries (has a chart that shows deliveries by Country and another that shows deliveries by Product Family)
-Orders List (hidden page, shows a list with all the orders)
-Deliveries list (hidden page, shows a list with all the deliveries)
I want to reach the 2 hidden pages via drillthrough.
Using the old "right-click" way I would need 2 country columns and 2 Product Family columns. That is because if I use the same column, both "Orders List" and "Deliveries List" would be reachable from both "Orders" and "Deliveries". This is not what I want however, I want to reach "Orders List" from "Orders" and "Deliveries List" from "Deliveries". Again, you need to have Country 1 and Product Family 1 as axis in the "Orders" page and as drillthrough fields in the "Orders List" page. And you need to have Country 2 and Product Family 2 as axis in the "Deliveries" page and as drillthrough fields in the "Deliveries List" page.
This is the only way to avoid reaching everything from everywhere with the right-click drillthrough feature.
With the button drill-through you have the possibility to avoid this. You can use DAX to specify the destination page of the drillthrough and this allows you to use only 1 version of the columns as axis and drill-through fields.
In our basic case you would simply need 2 measures
DrillthroughOrders = "Orders List"
DrillthroughDeliveries = "Deliveries List"
You then create a button with drillthrough feature in each page and use the respective functions in the destination field to reach the correct page via drillthrough.
However this comes with 2 problems:
1st PROBLEM: (and I haven't been able to solve this)
Drillthrough remains active with the right-click version. Since there is no way to de-activate drill-through on a visual (not that I know of) the user will still be able to right-click and see drillthrough on the visual. Since now you are using only one 1 column to do all your drillthrough (and you customize the destination in the button with DAX), the user will see ALL drillthrough destination with the rightclick. This is not a desirable behaviour and it might also lead the user to see incorrect data
Could this be solved with a TREATAS? I have to try it out (I came up with this idea while writing)
2nd PROBLEM: (and I've solved this with a workaround)
The button does not allow you to multiselect visuals with CTRL and then drill. Or to better explain: if you have 2 drillthrough fields in the hidden page (Country AND Product Family in our case) you won't be able to multiselect and drill. It tells you that you can only select ONE of the two and then drillthrough.
You can however multiselect with CTRL and drillthrough if you have only 1 drillthrough field in the destination page. If, for example, you only put country in the hidden page, then you will be able to multiselect 1 country and 1 product family and drill.
Doing this however you lose the possibility to drill by product family, the other element on the page that you want to drillthrough. That is because you have removed "Product Family" as drillthrough field.
SOLUTION: you need to create 2 identical copies of the hidden page
1 will have country as drillthrough field
1 will have product family as drillthrough field.
Then, in the custom destination field of the drillthrough button, you put the following DAX (or something similar)IF(ISFILTERED(Account[Country]) && NOT(ISFILTERED('Opportunity Product'[Product Family])),"Orders 1","Orders 2")
In this way the drillthrough will activate if you select only Country, only Product Family, or both. The user will be brought to two different pages depending on his selection, however to him it will look the same. You need of course to create a function for each custom destination and repeat this procedure
The problem with this solution is that while you can use only 1 version of a column to do all your drillthrough, you need more than 1 copy of the destination page. So you will have a cleaner data model but a messier report
Depending on your requirements, you will have to choose if you are better off with the right-click or button drillthrough
That's all I find out today about the pros and cons of the drillthrough buttons and the way to use then, I hope this is helpful to someone