Power View

My Thoughts on Cross Filtering in Power BI

My Thoughts on Cross Filtering in Power BI

September 30, 2015

It’s amazing how much of an influence your upbringing can have on you and your preferences. I was the youngest in my family for almost 10 years (till my brother came along) and not to say, growing up, I was very much pampered by my mom. However, my dad was more of a proponent of what I call tough love and back in those days, it was still legal to spank your child to set him straight. (Sometimes, I even feel like my father was the one who coined the proverb – Spare the rod and spoil the child). Looking back now, I feel those were the moments that really formed my character and helped me reach where I am, and I am really thankful to God almighty for giving me the perfect family. I know some of you might be nodding your head in agreement with me reading this post, while a lot of you might be getting really angry at what you are reading. This is perfectly understandable, and it’s ok, because we live in a free country, right? You might also be wondering the reason of such a lengthy introduction to my blog. Well, the reason is that today’s post is kind of tough love as I am criticizing (constructively) the way Cross Filtering works in Power BI, and also providing an example of how I think cross filtering should work in an efficient BI tool by showing Tableau as an example.

My thoughts on cross filtering in Power BI

Action Item – Please do it

For those who don’t have time to read this post, I would please request two things:-

1) Please vote for this issue (link given below) and tell others to also do it https://support.powerbi.com/forums/265200-power-bi/suggestions/6709520-drill-down-should-drill-or-cross-filter-other-visu

2) If you are reading this before 12 PM PST Oct 2, 2015, please take the below survey which will give the feedback directly to the Microsoft Power BI research team (and if you are in the US, you might win a $50 Amazon gift card also). Make sure that you mention “making cross-filtering more intuitive” and “ability to hold selections on more than one chart for cross-filtering” as two points for this question in the survey– “What would make Power BI Desktop a better experience for you?

Please please please do it Smile

How Cross Filtering in Power BI works

Before I start this post, I have to say that I am one of the biggest fans of Power BI, and I have never been as optimistic about a Microsoft BI product as I am right now. (and it’s not just me being a fan boy, the most recent Forrester Wave report shows Microsoft leading the BI pack)

Forrester Wave BI report

The Power BI team is also one of the most responsive teams and you can regularly see the product managers as well as the product team members interacting with the general community on twitter and the Power BI community. But they don’t have an infinite number of resources and time, and hence will be making changes to the product based on priority, and votes are one way we can help the team prioritize the feature requests. The more the votes, the higher the priority and the faster we can get this feature implemented by the product team and this is where I really need all of you to pitch in. Let’s start by taking a look at how cross filtering in Power BI works today:- Let’s say I have 3 charts – bar chart for sales by business lines, bar chart for sales by country and a bubble chart which shows some KPIs by countries.

Power BI dashboard

You can click on any chart and see the rest of the charts refresh for it. For eg, I can click on Nutrition business line and see the other charts refresh for it. However, there are a couple of things I can not do. For eg 1) I want to see the bubble chart for business line – Nutrition and country – Japan. Normally you would expect to click on Nutrition in the first chart, and Japan in the bottom chart to see the cross-filtered bubble chart. However, Power BI currently does not allow us hold selections on multiple charts. The workaround is to add slicers or filters for the needed fields, however it will break the flow when I am trying to find insights from my dashboard.

1 Power BI not able to hold multiple selections

2) I want to see where a particular country is in the bubble chart along with the rest of the countries, so I can assess it’s performance with respect to the other countries. For eg, when I click on Japan in the bottom bar chart, I want Japan to be highlighted in the bubble chart. Right now, you can see from the above image, that when I click on a particular country, only that country appears in the bubble chart because it is filtered. What we need is an option to specify whether we need a chart to be filtered or in this case, highlighted. 3) Cross-filtering during drill downs is the most counter-intuitive feature for me. I had raised this issue in 2013 for Power View and despite getting a lot of votes for the connect issue, the issue is still active. For eg, let’s say the bottom chart shows the sales by Region which can be drilled down into countries. When I drill down into the Greater China region (which only has 3 countries), I expect the top chart to cross filter for Greater China region, which it doesn’t. But if I manually select all the countries, it will cross-filter the top chart appropriately. So in a way, I can take a screenshot of the exact same report showing 2 different data points – which would be very confusing for end users. What we need is for the cross-filtering to work intuitively when we are drilling down.

2 Cross-filtering during Drill down is counter-intuitive

And looks like I am not the only one as I can see 4 comments in the last month on this.

Comment from Microsoft Connect

Comment from Power BI Community

How I feel Cross Filtering should work

I didn’t want to sound pompous by saying this is how it should work; everyone has their own ideas and most of the times, no-one is wrong. But this is why it is important for a BI tool to give options to the end user, so that they can manage the options and choose to use it the way they like. That said, let’s take a look at another popular BI tool – Tableau and see how it handles the above scenarios.

1) Note how the entire report cross-filters as I keep on holding multiple selections.

3 Tableau cross filtering

This experience is very important as I can see what are the top countries for each business line, and then I can also choose a particular country and then see the information for the selected country and business line to analyze in detail.

2) Also note how the country in the bubble chart gets highlighted, when I click on a country in the bar chart. As the number of bubbles increases, it is difficult to see where the selected bubble is unless we have a highlighting feature. The reason why highlighting is important is because it will help us identify patterns by comparing with the rest of the categories.

3) As far as the drill down is concerned, it is not that straight forward in Tableau, but we have options that will help us achieve the end result as I have shown below. You can see that when I click on a particular product category, it drills into the subcategory and all the other charts (the bar chart on the right and the table under it) are also getting filtered appropriately.

5 Tableau drill down

In reality, they are 2 different dashboard sheets, and clicking on the first sheet takes us to the second sheet with the drill down parameters intact (just like with SSRS). But an end user will not be able to get this difference and at the same time achieve the functionality.

Conclusion

It’s amazing how much ground has been covered in Power BI ever since the release, and there have been some really great decisions (like the ability to add custom visualizations – you just have to look at some of the contest entries to see some really great dataviz) as well as features (44 new features in the last monthly release!!!). For all that we know, the team might have already made this change in their next monthly release, or maybe it is still not in the priority list because enough customers do not want this. Either ways, I just wanted to put this out in case you also think the same way, and if yes, make your voice heard in the Survey. Now would be a good time to scroll up to the Action Item part! Smile

Note

As I mentioned before, Power BI versions change rapidly and there are a lot of new features coming in monthly. So it is important to check your version and see if there are any changes. The version at the time of writing this blog is given below-

image

Posted by SQLJason, 5 comments

QR Codes in Power View

November 19, 2013

Today, I was reading a good article by Florian Mader – A GPS Photo Gallery in Power BI. That inspired me to write something on Power View and since I had not originally planned to write a blog today, I decided to write something short.   QR Codes in Power View

QR Codes are not available by default in Power View and it is normal to hear many people say that it is not possible in Power View. The reason behind such a conclusion is because Power View is not at all flexible and you don’t even have simple options to change your chart colors, let alone program a new chart type (which is not a bad thing, as the focus is on ease of use and simplicity. More options would make the tool more complex). However, in this case, we can utilize the power of the internet and Image URL feature of Power Pivot to generate QR Codes. For this post, I am using the model below with some sample data.

Model + Sample Data

The fact table consists of Customer name, Brand Name, Date and Sales. The requirement is to display the QR code for the selected Brand. For that, follow the steps below:-

1) To create the QR code, it is necessary to create a calculated field in the data model. Since the QR code should be there for each brand, the calculated field should also be in the Brand table. The formula for the calculated field QRCode is given below

=”http://qrcode.kaywa.com/img.php?s=8&d=” & [Brand] & “” & [URL]

QR Code Formula

My regular readers might remember this URL from my post – Generating QR Codes in SSRS. There are other QR code generating sites also, I am just using this as an example.

2) Once the URL is generated, make sure that the Data Category property in the Advanced tab is set to Image URL.

Data Category property

3) Now we can use the QRCode field in Power View to display the QR Codes. You might get a warning to enable external content, which is normal.

QR Code in Power View

4) We can also use it along with other visualisations like shown below.

QR Code along with other visualizations in Power View

The output of the QR Code from my smartphone is given below:-

Output

Limitations Now that said, there are a few limitations. It is absolutely necessary to have a calculated field to make the QR Codes. This means that the values will be pre-computed and we can not add a measure or some value that changes during the run time of the report to the URL of the report. Also, we can not make much changes to the size of the QR Codes as we don’t have a lot of control on that. This technique can also be used to get other chart types into Power View. However, the same limitations will apply. That is all for now, time for me to get back to baby-duty Smile.

Posted by SQLJason, 0 comments
Drill-down Behaviour in Power View

Drill-down Behaviour in Power View

July 25, 2013

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.

Drill down in Power View

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.

Source Data

I) How to Implement Drill-Down in Matrix

1) Open Power View and then select the three columns to make a table.

Simple table visualization

2) Click on the Table icon and select the Matrix option. Now you can see that the table has been converted into a matrix.

Convert table to Matrix

3) Now select the Show Levels icon and click on Rows – Enable Drill Down One Level at a Time.

Enable drill down

4) You can see that we have achieved the drill down functionality in the matrix. Click on US and see the results.

Drill down in matrix - Power View

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.

Chart in Power View

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.

Drill Down in Chart - Power View

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.

Chart and table in Power View

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.

Chart cross-filters table

3) Next, see what happens when you double click on a country (say India).

Double clicking in chart removes cross-filter in table

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.

CTRL+Click on the three bars -> table is cross-filtered

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.

Click to vote

Hopefully we will have enough votes for Microsoft to look into the issue seriously!

Posted by SQLJason, 11 comments
Creating Maps in Excel 2013 using Power View

Creating Maps in Excel 2013 using Power View

July 18, 2012

There has been a lot of buzz in the BI community since yesterday, and the reason is none other than the public preview of Office 2013 (Excel 2013 in particular). There’s been a lot of articles written on the new features available and the integration of PowerPivot and Power View has really got everyone talking about. In case you still haven’t looked at the new features, here is the link for it. Meanwhile I can’t wait to blog about the spatial capabilities available in Power View, so I am directly jumping to the topic.

Creating Maps in Excel 2013 using Power View

Power View in Excel 2013 has the ability to create maps from your data and uses Bing maps for the same. Unlike the maps in SSRS, here you do have the ability to zoom and pan as needed. Also, Bing maps automatically detects the location and hence you don’t need to provide a shapefile or even the latitude/longitude information. In this blog, I will take you through the steps to create a map report in Power View.

I) Creating a basic map report

1) Open up a new workbook Excel 2013 and then enter the following data in the cells

Country City Sales
USA Charlotte, North Carolina 100
USA Madison, Wisconsin 50
USA Jacksonville, Florida 140
USA Rochester, New York 40
USA Philadelphia, Pennsylvania 120

2) Select the entire data, go to the Insert tab and click on Power View icon.

select data in excel

You should get a loading screen while it takes a couple of seconds to open Power View

loading power view

3) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map icon as shown in the image below

Map in Power View

You should get a warning to enable content as the data needs to be sent to Bing to get geocoded. Click on enable content to proceed. Note that you would need an internet connection for implementing this.

enable content in Power View

4) Now you can pretty much rearrange your fields by dragging them into the areas below. I have dragged Sales into the Size, City into Locations as well as Color. So I get a map report below which shows the cities as bubbles with corresponding colors and size as the amount of sales.

Basic map report in Power View

5) You can also play around with other properties like Title, Legend, Data Labels and Map Background. They are present when you click on the Layout tab.

map layout properties

It is interesting to note that the data used for creating the Power View report gets imported into PowerPivot by default. This is because Power View can only communicate through DAX currently, and hence needs a tabular model behind it. The PowerPivot model can be viewed by clicking on the PowerPivot tab and then selecting the manage tab.

powerpivot

Since there is no way to add additional data into this model without deleting and recreating the table, it would be a good practice to create the PowerPivot model first from linked tables, and then using the PowerPivot fields to create the Power View report. This way, we will be able to keep on adding data as long as there is a link between the table and PowerPivot. I will be showing you how to do this in the next part.

II) Creating a drill down in map report

1) Select the same set of data in excel, go to the PowerPivot tab and select the Add to Data Model icon as shown below.

Add to data model

Note that you can still create Power View reports directly, but we will be using this technique for the reasons mentioned above earlier. 2) Select the home tab, then click on Pivot table option and select Power View to create Power View report.

PivotTable-->Power View

It is recommended to set the reporting properties of the Country and City field, so that Power View can recognize them as geographical entities, as shown in the image below.

setting reporting properties

3) Now you should be able to see Power View (in case you don’t, you can click on a blank cell, go to the Insert tab and click on Power View icon). Notice that the Country and City fields have a map icon

map icon besides fields

4) Now click on the Country field, and then you should be able to see the Map icon appear on the menu above. Click on the Map and then drag both the country and city fields within locations. You can also put the Sales measure in the Size area. Now you should have a map report which drills down from the Country level to the city.

Adding drilldown fields to location

If you double click on the blue dot in the center (which is USA), you will get the drill down report by cities. Notice that the title has automatically changed from Sales by Country to Sales by City. You can also click on the up arrow (highlighted in the image below) to return to the parent report.

return to parent report

You can also create hierarchies in your PowerPivot model, and that can be directly added dragged and dropped to the Locations area. This will ensure that you don’t have to drag and drop each field individually and the drill down would be present across the entire hierarchy. You can also see the benefit of adding the linked table to the PowerPivot model instead of using a range. Now if I have to add data, I can just go to the excel sheet and append the rows that I want as shown below

Adding more data in linked table

Now, all I need to do is to go to my Power View sheet and refresh the report. You can see that the new data is already included in the report

power view report showing new data

III) Creating Pie charts in map report

1) Use the previous report, and then add the city to the Color area. Now you can see that there is a pie chart by cities at a country level.

Add city to color

2) You can hover the mouse on the pie charts, and the pie chart will expand and show the tooltips as shown below

Pie charts in Power View

You can also use the horizontal / vertical multiples location areas to split it by the selected field as shown below

multiples in Power View

Similarly, you can use the Tile By option also (however, this feature is not available if you use the multiples option) Another best practice is to concatenate your city names with state/country info also (as I have done in all the examples) as there might be duplicate city names and doing this will help Bing in geocoding it better. You can also do this operation as a calculated column in PowerPivot, if you don’t want the city names to be displayed with their country/region info appended.

IV) Creating a map report with latitude and longitude

1) Let’s say we already have fields in the database with longitude and latitude, and we want to use them instead of Bing geocoding for us. For the purpose of this demo, let’s use the data below and paste them into excel

Country City latitude longitude
Australia Melbourne, Australia -37.8136 144.9631
Australia Sydney, Australia -33.8737 151.2069
Australia Brisbane, Australia -27.4709 153.0235
Australia Perth, Australia -31.9529 115.8573
Australia Adelaide, Australia -34.9287 138.5999

I used the site http://www.findlatitudeandlongitude.com/batch-geocode/ to get the latitude and longitude of the address. 2) Now select the data and use that to insert a Power View report. Click on the City field and then select the Map option. Now the latitude and longitude would be mapped to the corresponding columns if the names are the same (else you can always drag and drop them) and your map report would be ready.

Power View report by latitude and longitude

Hopefully this post has given you a good start to start playing with your own map reports in Power View. There are again a lot of best practices embedded within the post and sorry for not doing a great job of consolidating them, but I am hoping that this will serve as an incentive for you to read this long post completely. And well, if you have not yet downloaded the office preview, do it now from this link.

Posted by SQLJason, 22 comments
Tracking Data over Time Visually in Power View aka Project Crescent

Tracking Data over Time Visually in Power View aka Project Crescent

October 18, 2011

All right, I know most of you Microsoft BI fans would be gushing over the new announcements made in PASS Summit 2011 last week. Now onwards, Project Crescent will be officially known as Power View, SQL Server Denali as SQL Server 2012, Juneau as SQL Server Data Tools and finally, we have a mobile BI strategy! (you can read the official press release from here and some more details on Power View from here). I decided to celebrate the occasion by blogging about one of the new features I particularly like in Power View in CTP3 version – tracking data over time visually. For this demonstration, I have used the computer in SQL Server Virtual Labs. This is a great place to kick-start your SQL Server 2012 journey. Apart from the great tutorials, I also access it when I have to test out some new feature. Follow the steps below to create a Power View report with the ‘track data over time’ functionality:-

1) Go to the SharePoint site and click on Data Sources. Then click on the small dropdown icon on the right of the data source name and select the Create ‘Crescent’ Report option as shown in the image below.

1 Create Crescent Report

2) If this is the first time you are opening the Crescent report, there is a chance that you might get the following error –
An error occurred while loading the model for the item or data source. Verify that the connection information is correct and that you have permissions to access the data source.

2 First time error when opening Project Crescent report

This is a bug within the current CTP and this should go off if you refresh the address bar of the browser.

3 Crescent report layout

3) Select Category under Product and Quantity under Sales. A table will be displayed in the layout with the values of Category and Quantity.

4 Selecting Category and Quantity

4) Click on the Scatter chart option as shown in the image below.

5 Selecting scatter chart

5) Resize the chart to occupy the full space. A scatter chart requires 3 measures – for the X axis, Y axis and the size of the bubble.  Since we have already selected the Quantity measure, we just need to select 2 more measures for the Y axis and size. Select Revenue and # Products.

6 Selecting measures for scatter chart

6) Once that is done, select Year under Date and this will automatically be taken as the Play Axis of the scatter chart. You should be able to get the graph as shown below.

7 Select Year as the Play Axis

7) Give a title and then click on the save button on top left corner. Enter the destination path as well as the file name and then click ok to publish the report in SharePoint.

8 Publish reports

8) Go to the destination path specified in the step above and click on the report name to preview it. Click on the play button to see visually how the Category changes over the year.

Track Categories over Years

You can also click on a particular category and track the changes for that particular category over the year.

Track Selected Category

Isn’t that sweet and simple? Smile Coming from a manufacturing domain, I can already think of lots of scenarios where this can be used (e.g.: tracking price changes, profitability, revenue, stock, sales, etc over time). Of late, I have come to realize that some people face difficulties in following blogs by just reading the steps or by seeing a few images. So, I have taken a conscious decision to video-blog the steps whenever I can. You can find the video for this blog below

Tracking Data over Time in Power View

Let me know your feedback about this initiative and whether it is helping in conveying my message better. Cheers!

Posted by SQLJason, 2 comments