Month: November 2013

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

=”” & [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:-


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
Category Shading for Regions in Power Map

Category Shading for Regions in Power Map

November 12, 2013

It’s been a really busy month for me professionally (with the PASS Summit 2013,  SQL Saturday Charlotte BI Edition, etc) as well as personally (with a steady stream of family flying in to visit the new born baby as well as looking after a new born baby – how I miss my 8 hrs. of sleep!). I remember people saying to me that things are going to change drastically once you have a baby. Well, what can I say other than it’s 100% true Smile. Understandably, I don’t get the same amount of time to spend on blogging and with all the stuff that has been happening behind the scenes, I decided to procrastinate till everything gets a little bit more calmer. That is when I was reading through an article and fell upon this gem of a line from Zip Zaglar – “If you wait until all the lights are “green” before you leave home, you’ll never get started on your trip to the top”. So I decided to shake off my laziness and get started on blogging about a cool feature in Power Map that has not been explored or blogged much – Category Shading for Regions.

Category shading within Regions in Power Map

The objective of this post is to explore the category shading feature in Power Map as well as try to see what kind of questions can be answered (after all, a feature that just adds flashiness and no business value is not desirable). For this post, I will be using the stats for my blog for visitors from the USA by browsers.

Sample data from my blog stats- 2013 data

With this data, I created a simple Power Map visualization which shows the number of visitors by state. (Check out this link if you are new to Power Map or need more info on how to create this visualization).

Region shading in Power Map

With the above visualization, I can answer questions like – Which state in the US has the most number of readers? I can easily see that California and Texas are where I have got the most number of readers. Now that I have a general understanding of the visitors, I am going to ask a few more specific questions based on the browsers that they are using:-

I) What is the dominant browser for my readers in each of the states?

To answer this question, bring the Browser field to the category and now you should notice an icon which appears on the top-right of the category textbox (denoted by 2 in the image below). Click on the icon and select the No shading option as shown below.

Category shading in Power Map - Show full value

Now, I can see the results for myself. Interestingly, the dominant browser in almost all the states is Internet Explorer. There are a couple of states (6 to be precise) where Chrome is the dominant browser. This brings me to my next question.

II) How dominant are the leading browsers within each state with respect to their competing browsers in that state?

Click on the category shading icon and select the “Shade based on category value within location” option. Now you should see the below visualization.

Category shading in Power Map - Shade within location

From this, you can easily see the leading browsers based on the color as well as the shading shows the share for the leading browser. The darker the shade for the color, the more dominant the browser is within the region. For eg, you can see that Internet Explorer is pretty dominant in Alaska as well as North Dakota, while Chrome is pretty dominant in Wyoming. In California, you can see that IE is leading but not by much.


However, when I saw the actual numbers in the tooltip, I found it to be really less. This brought me to my third question.

III) Which states have more visitors and what is the dominant browser there?

Click on the category shading icon and select the “Shade based on category value compared to all other values” option. Now you should see the below visualization

Category shading in Power Map - Shade compared to all other values

You can see that it is a refined version of the map which was obtained initially by not putting the category field. The darker the color, the more number of visitors I have. So from the above image, we can easily see that the top 4 states by readership counts are California (Internet Explorer), Texas (Internet Explorer), Washington (Internet Explorer) and North Carolina (Chrome). And now looking at the previous image (for Question II) in combination with this image, we can see that the difference is not much and that the competition between the browsers are pretty tight across the states. Also, for the three states where the difference is really big (Alaska, North Dakota and Wyoming), we can see that the counts are pretty small and hence not that significant.

IV) How do the states within the same dominant browser measure up against each other?

For this, click on the category shading icon and select the “Shade based on category value compared to other values in the category” option. Now you should see the below visualization.

Category shading in Power Map - Shade compared to other values in category

From this image, I can see that for Internet Explorer, the top 3 states are California, Texas and Washington & for Chrome, the top 3 states are North Carolina, Iowa and Utah. It is important to understand that there might be other states which has more value for that browser. For eg, California might have more readers using Chrome than North Carolina, but it is not taken into account since Chrome is not the dominant browser there. Time to finish this lengthy post and this will be all from me today. But before I sign off, I wanted to let you know that you can animate this kind of visualization across time by dragging the date field to the time-play axis. This can answer the same 4 questions we asked above across time, which is pretty powerful. Also, we can look at the existing set of data with Power View and answer more interesting questions – a sneak peek of a simple dashboard is given below. Have fun with Power BI till we speak again! Smile

Power View dashboard - Browser Wars

Posted by SQLJason, 3 comments