I usually don’t write a lot of posts asking for favours from my readers (in fact, this is the first one), but an incident happened today at my client site which has aggravated a pet peeve that I have with Power View – drill-down behaviour in Power View. The drill-down behaviour in Power View is really unintuitive and this is at least the the third client of mine that has raised this issue of drill-down. That is when I decided to raise my voice and ask for your help too in letting Microsoft know about this issue, so that they can consider changing this in a future release. For those of you who don’t have the time to read this post of mine, I would really appreciate if you could vote this Connect issue that Leonard Murphy has raised. For those of you who have the time, follow my post below to see the current drill-down behaviour in Power View and why I feel that this is not right.
For this demonstration, I am using Power View in Excel 2013 (although this behaviour can be reproduced in Power View for SharePoint also) and a very simple table (which shows the sales by countries and states) as my source.
I) How to Implement Drill-Down in Matrix
1) Open Power View and then select the three columns to make a table.
2) Click on the Table icon and select the Matrix option. Now you can see that the table has been converted into a matrix.
3) Now select the Show Levels icon and click on Rows – Enable Drill Down One Level at a Time.
4) You can see that we have achieved the drill down functionality in the matrix. Click on US and see the results.
Also note the drill down and drill up icons.
II) How to Implement Drill-Down in Chart
1) As in Step 1 of previous section, select all three columns to make a table. Then click on the Bar Chart option and select Stacked Bar. Ensure that the Country and State are in the Axis and Sales is on Values.
2) You can see the drill-down functionality by clicking on Canada. Also note the drill-up icon on the top right of the chart once you have drilled down.
III) Unintuitive Cross-filtering behaviour
1) Everything looks fine when you just have one chart in the canvas. Now let’s see what happens when we have 2 visualizations – lets say, a bar chart with drill-down and a table which just has State and Sales.
2) Now when you single click on a country (say India) in the chart, the table gets cross filtered for the states of the selected country.
3) Next, see what happens when you double click on a country (say India).
You can see the bar chart has drilled down into the states for the selected country as expected but look what has happened to the table. The table is now showing states for all the countries and not just the states of the selected country. This is so unintuitive as a person seeing the report will expect to see only the sales of the three states of India. This can get even more confusing if the states are not displayed on the table, and there will be no way of knowing what has been cross-filtered on the table.
4) To add to the confusion, I can press CTRL and then click on all three states. Look at the image below for the results.
You can see that the bar chart looks exactly the same as the previous image, but the table looks different. In short, the report looks exactly the same and there is no way to differentiate whether the data in the table is correct and if some cross-filter has been applied or not. What I would expect is that the drill-down always cross-filters the rest of the visualizations. Honestly, I can’t think of a scenario where I would not want the cross-filtering to happen and I would love to hear your views as comments.
IV) Call for Action – Vote up the Connect issue
If you agree with me that the cross-filtering behaviour is so non-intuitive, please vote up the connect issue. You can do so by clicking on this link, log-in and then clicking on the green up arrow.
Hopefully we will have enough votes for Microsoft to look into the issue seriously!
Thanks for posting this. What a pleasant surprise. 🙂
I would imagine this issue is going to come up for anyone who builds a Power View report that's meant to be used by actual people.
Completely agree with this and have voted it up!
Another thing I'd like to is the ability to drill the legend of a bar chart. Unless I'm missing something it's only possible to drill the x-axis at the moment and as that is often Date/Time it really restricts how useful drill is.
done! 🙂 hope we can get this one soon! 🙂
Thanks for the support guys! Hopefully, someone from Microsoft notices this and gets it sorted out. 45+ votes in less than a week, hope that is enough!
Nice post, Jason! It's helpful feedback. Thanks for writing it.
Re: Part III, #4 ("To add to the confusion, I can press CTRL and then click on all three states…"). You've shown a good example of what I believe to be an issue with chart data point selection, not specific to drill. There's no visual distinction between data points that are explicitly selected and those that are merely in their default state (i.e., nothing is selected) or highlighted by other selections. As your example shows, explicit selections are significant and affect the data in other visuals on the canvas. We're looking at ways to visually distinguish explicit selections, which would address this issue.
Back to drill, I agree that drill-down should probably cross-filter or cross-highlight other visuals on the canvas. I know some would disagree, but I think there are stronger reasons for doing so than for not doing so. Regardless, I certainly agree it seems counterintuitive for a single-click on India to cross-filter other visuals by India, but a subsequent double-click on India to undo that cross-filtering.
Now, with that said, drilling into India and preserving the selection on India would pose an interesting problem: India is no longer visible, so now you have "invisible" selections. I have some ideas for how we could address that issue (just for example, providing some breadcrumb or restatement in the chart, integrating with the Filters Area, etc.), but I'd love to hear any other thoughts on the matter.
Hope that helps!
Riccardo Muti
Microsoft
Hi Riccardo,
First, a big thanks for noticing the issue and taking the time to respond.
Regarding drill-down, I can't think of a valid reason why you would not want to cross-filter when you drill-down and I am always open to hearing other thoughts. Most of the people I have talked to also seem to agree with me that it is really counter-intuitive.
As for the "invisible" selections, I was also thinking of bread-crumbs on the top of the chart. However, it should be done in such a way that it doesn't make the Chart really small (especially if there is a lot of multi-select values). The other option I see is to introduce a Cross-Filters pane (similar to the Filters pane). The difference with the Filters pane would be that Cross-Filter would not filter out the data like Filter does, it would just shade them in a different color. This way, at any point of time, we can see what has been cross-filtered and this can also help in resetting the cross-filters more intuitively.
Thanks, those are very much in line with the two ideas that I mentioned and that we're exploring :). One approach is a breadcrumb/restatement in each visual (I agree screen real estate would pose the issue it often does). Another alternative would be some integration of cross-filtering/highlighting selections into the Filters Area.
Hey Jason, just voted up on this. I agree with the intent you just described, but here's another scenario.
What if someone wanted to drill-down in the table or matrix at right, but leave the overall Country viz untouched on the left. If cross-filtering were always on, one could end up at an undesired level on the regional summary on the left.
Today, analysts want the ability to see all the details, not just the next level down, and the next, and the next. This kind of thing is old, slow to answer questions, and opens more questions. "Invisible" selections? You've got to be kidding me. Why not just give people access to the detail data, straight up? Drill-through actions comes to mind. Or maybe a way to click on a viz or table, and have it connected to the next view in your rdlx with the next set of vizes awaiting your selection.
Regardless of the interactivity and user experience, you'll always end up with a void if people can't access the details.
So what we should probably stop doing is looking at "drill-down" the way we've always seen it in Excel and SSRS, and start looking at ways to drill-across views and/or viewing detail data. I think we should look at ways to do this in the context menu so its not intrusive to the data vizes or tables and matrices.
I also hope that regardless of what Microsoft does, they keep it touch friendly. The SSRS expand/collapse button for drill-down functionality is such a pain on a mobile device. Double-clicking or double-tapping is also old school and easily unnoticed or unused.
Angel: Thanks for voting + commenting, always good to hear to hear from a fresh perspective.
As for the scenario you said, I still believe that the cross-filtering (and not filtering) should work (which means that the table on the right shows the drill down data -lets say, states for US, while the viz on the left should contain the cross-filtered data – country for US is highlighted, while all the other countries are still there in faded colors. This way they can easily see the regional summary on the left and yet see that there is a cross-filtering happening. This feature is very important or else there might be cases where the "invisible selections" come into play.
And no doubt, drill-through and drill-down to another rdlx are all features that are very important and I would love to see them in Power View. But I think I can see the concerns / questions arising for the Power View dev team. Features like this can increase the complexity of the tool (in short, Power View would be a better version of SSRS with interactivity if this happens, and we know SSRS is not a business friendly tool) and the ribbon is already crowded. But I think the advantages outweigh the disadvantages, and these features need to be added to make Power View more competitive in the market. This might also bring in the need for a desktop client for Power View (similar to BIDS / SSDT for SSRS) and this client can have all the advanced features (if adding these features to the web version is not possible).
Touch + Mobile friendly is another key feature, as you rightly mentioned. This is another pain point I have usually faced while trying to sell MSBI to my clients. Let's hope Office 365 Power BI would help alleviate that pain!
Will catch up with you on this stuff at the PASS Summit! 🙂
Angel, thanks for your comments too. I agree that hierarchical drill-down doesn't address every scenario and doesn't provide detail-level answers, but then I could argue the flip side for drill-through and drill-to-details: navigating to another view or drilling all the way to detail-level data isn't always what you want either. I think we want to provide users with a small set of interactions that complement one another and work well in concert to address a variety of scenarios, and I think drill-down, drill-through (to another view), and drill-to-details all have their place in that mix. We prioritized adding drill-down first (so this discussion started about drill-down), but drill-through and drill-to-details were and remain on our radar as well.
Good discussion, and I guess I'll see you both at PASS Summit :).
Riccardo Muti
Microsoft
Thanks guys. I unfortunately won't be at the PASS Summit this year, but I will be at the PASS Business Analytics conference next year. Hopefully speaking again but you never know. So I want to get this out of the way.
First of all, I apologize for suggesting drilling to another view. Borrowing from Stephen Few's book "Now You See It", what I am requesting be added is Drill to Details: 'A way to access the details without departing from the rich visual environment'. He suggests that this should be as simple as a single mouse click, but I think a left and right mouse click with an additional dialog box in between is ok (ie. Tableau's method). Kind of like viewing details in Performance point with a smaller window off to the side to not detract from the Power View visualization.
Drill to details is one of the most frequent requests by clients. Being a manufacturing engineer at one point in my career, I can understand why. Yes, data visualization is good for identifying trends and patterns, but is no good unless you understand why. So if I had to back out of my Power View report, remember all the selections and filters, go back to SSRS or Excel and apply all the same logic to try to answer "why", then I've just lost a major opportunity to sell this tool to a broader audience.
Microsoft is a lot closer to having an answer to many BI needs with a single tool. I have much hope for Power View. Take a look at Chapter 5 in Stephen Few's book. He's got a lot of pointers in what to look for in visualization software, not just Drill to details. I'm doing a project inside my company looking at various visualization tools, and I'd like to be able to check a box next to Power View under all the Analytical Techniques and Practices. Unfortunately, drill-down isn't even mentioned.