Data and Analytics Resources

Provide Confident Assurance to Your Organization

How to Input Static Resources Into Power BI Using Azure Storage

by Mike Druta

Oct 23, 2017

We’ve all heard of telling a story with data, but what if you could add images to your dashboard to paint the picture even further?  In this tutorial, I’ll demonstrate how to store static images using Azure Blob storage, with the help of Power BI calculated columns and Image URL data category to dynamically display those images on a dashboard.  

Let’s set the scene, you took your sales team on an Indoor Skydiving adventure for a team- building exercise. They tasted the wind tunnel, held hands, and managed not to hurl. That exercise worked to some extent, but now you also want to build a dashboard showing their performance against quota. To make it less boring, you want the dashboard to dynamically show the rep picture when selected. To do this you will have to have a column in a Power BI table which provides the URL of an image. The URL has to use “https”; simple “http” is not supported. The questions that always comes up when a use case like this shows up is “where can I quickly host these images and serve them over http?”

For our example, let’s assume that you don’t have a web development team, or you don’t want to go to them with something so trite. But you do have an Azure subscription, or you know you can get one for free or at a very little cost (all Visual Studio licenses come with an Azure subscription and anywhere from $50 to $150 per month to use as you like).

One quick and extremely cheap option is to use Azure Blob Storage to hold and server images over https. Let’s see how we can quickly set this up. Your first step is to open your Azure subscription and create a storage account. The settings you choose may be different, but a Standard, locally-redundant (LRS) blob storage account like the one below will cost you about $3.16 / 100 GB / month. If this did not scare you away, read on.   

Image 1

Once the storage account is created, we first need to create a container (just a fancy name for a root folder, in our example it is called “salesteam”) and set the Access Policy to this container to public. This is important; remember that we will be freely serving these images to people that do not have access to your Azure subscription. 

Image 2

We can now upload the image files we want to use for our sales team. To do that, first download and install Azure Storage Explorer from this link:

https://azure.microsoft.com/en-us/features/storage-explorer/

When running Azure Storage Explorer for the first time, you will be asked to connect to a storage account like in the capture below. The values you need to successfully connect can be obtained from the Azure Portal, looking at the properties of your storage account. 

Image 3

Copy and paste these values into the corresponding fields in Azure Storage Explorer (note that either one of the access keys will work just fine).

Image 4

We are now ready to upload content to our container. As the capture below shows, we have uploaded three images. For this example, let’s assume the name of the image file matches the name of the sales rep. This will come in handy later on when we build our dashboard, as some mechanism for associating image names with the corresponding sales rep as it exists in the database will be needed. (Note that we could use IDs just as well.)


Image 5

 

After uploading a few images, we can now obtain the externally accessible URL for each one of these images. They will look something like this:  https://mdstorage0001.blob.core.windows.net/salesteam/greg.png. You can actually paste this into a browser and see that the blob storage is serving the images, but note that the URL is case sensitive. In other words, requesting […]/mary.png will work, but […]/Mary.png won’t.

Ok, it’s time to actually build our dashboard. For this use case, I’ve selected a very simple dataset containing the following fields: Rep Name, Quarter, Sale Amount per Quarter, and Quota per Quarter. Next, we can create a calculated column to supply the URL of the sales rep picture stored in our Azure Blob Storage. The DAX formula looks like below:
Image 6

One important note here. After creating the column, we have to tell Power BI to treat this as an image URL. To do this, on the Modeling toolbar ensure that the Data Category for our calculated column is set to Image URL. We can now build a simple sales dashboard that will use the new calculated column. The end result can look something like the one below:

Blob Storage GIF

The chart and filters are out of the box, but it is worth mentioning that the image is actually displayed is a table visualization. The table has one column only (“PicUrl”), and the column header is formatted to blend into the background. You can also use a Multi-row Card visualization.

In this brief tutorial, we saw how to store static images in a simple, cost-effective way using Azure Blob storage, and then use Power BI calculated columns and the Image URL data category to dynamically display those images on a dashboard. By adding imagery to the dashboard, you can increase interest to any plain statistics and further tell the story with the data provided.

To take your dashboard to new heights, contact a data professional at info@ccgbi.com or 813.265.3239.