Recently I was presenting a session on Microsoft’s Power View in what I had intended to be my final time presenting the session before retiring it to the archives. Unfortunately the presentation didn’t go as planned. For the life of me, I couldn’t find my Power View icon in the MS Excel ribbon, and began to go out of my mind! Note, I’ve discussed this topic at various SQL Saturdays and User Groups for the past couple of years, and it started to become somewhat of a second nature for me doing demo after demo of the various components and moving on to the next. So, it’s only natural that phrases like “it was just here” and “what did Microsoft do this time!!” came out of my mouth in front of the group, and I rapidly changed my focus and did what components of the demo I could show in PowerBI as an alternative. Later find out that, in fact, Microsoft did do something. With an assumed effort to get people to use PowerBI and move away from Silverlight, Microsoft made the decision to remove the button from Excel in both Excel 2013 and Excel 2016 as described in the following article by John P White:
A couple of lessons learned from this and, in a sense, re-learned: 1) every time you think you know what Microsoft is doing, you don’t. 2) do at least one quick dry run of your presentation immediately before the presentation, or as close to it as possible 3) Power View appears to be transitioning to a “legacy” product… I guess it was a good time to retire the session
Now that I’m doing some more development of dashboards in the Power View interface, I thought it might be helpful to post about some of the date “gotchas” I’ve run in to with the development interface. Power View is very nice about handling Date filters if you are able to specify a date you’d like to use as a start point, end point, or 2 dates to post data about between, however, if you’re looking to determine if a date range is say, “This Week”, “This Month”, “This Quarter”, etc… you’ll have quite a bit more trouble. The workaround I’ve found to solve this issue is to create a new column that results in a boolean (true/false) answer. Then within your Power View filtering, you are able to choose “IsPastWeek” = True, etc. For Example, in your formula bar (or whatever tool you’re using, excel, powerbi, or SSAS tabular):
The above formula, simply put, says If the date is later than today minus 7 days, then return true, else, return false. Below is a view of the filters in my Power View Dashboard:
By setting this filter, the ability to look at specific dates or time periods makes it easier to report graphically. Also, the ability to filter on separate objects within the same canvas allows us to easily compare similar time periods side by side.
While rehearsing my demo for my upcoming session at SQL Saturday Boston this weekend, #sqlsat500 where I will be lecturing and demonstrating Scratching the Surface of Power View, I noticed a quirky issue where I wasn’t able to drill through my column data, but was fine with row data. Further, after some playing, I was able to drill down if I changed the order of my column values, or tried using other fields. Everyone hates NULL data, and I think this is just another reason why. As it turns out, there are some NULLs that happen to show up as the first column when you drill down, so the ability drill “up” is lost, and the only way to get back to your top-level data is to close and re-open the report.
Using the AdventureWorksDW2014 Data, and building a basic example of creating a matrix, then adding the Drill Down properties, my selections look like this:
As you can see. A fairly simple example, just trying to capture the essence of Drilling Down and back out again. When I click to drill down in to the “Black” column, I get the following:
Note, the first column heading has no title, and there isn’t an available “Drill Up” arrow either. At this point, I’m stuck. I can’t go back up to my original report, and now need to close and re-open to start over. If I change the order of the columns so that “Style” is listed first, above “Color”, the Drill Down and Drill Up work fine. In order to workaround the issue, I replaced all the NULLs in the table with “NA” and Viola! Works perfectly. I’m not sure if this is by design from Microsoft in order to force the data to have values, but since it’s their dataset to begin with, I’m assuming it’s a bug.
Hope this helps anyone else running into this issue!