Tag Archives: Power BI Desktop

Power BI and Google Maps API (Address Lookup)

In this post I explain how to use Google Maps APIs to retrieve useful information out of Google Maps. The use case scenario could be getting address, postal code, etc. from existing latitude and longitude values. The data could be generated by any sort of GPS tracking device like your Garmin cycling GPS computer, your Fitbit watch etc. I know you can load your GPS tracking data into athletic social networks to analyse your activities. But, if you want to do some more specific data analytics like in which area of the city you created more power during your cycling activities then those websites might not give you what you want for free.

For instance, you can export your device data to CSV then import and append all CSV files into a Power BI model and create amazing analytical reports. How to import your CSV files into a Power BI model is out of scope of this article so I leave it to you for any further investigations.

GPS tracking devices are creating lots of data including geographic coordinates which can be easily used in Power BI. You can simply put latitude and longitude on a Map visualisation and you’re good to go.

Power BI Map using Coordinates

You can also concatenate the latitude and longitude data and use it as Location in your Map visualisation.

Power BI Map using Location

This can be done from Query Editor in M language.

Creating Location from Latitude and Longitude in Power BI

But, in some cases you need some more geo-information like Country, City, Post Code and Street Address in a table as well. Or you might want to use postal code in a slicer. In this article I show you how to get all of these information out of Google Maps by passing existing coordinates to Google Maps geocoding API.

Continue reading Power BI and Google Maps API (Address Lookup)

Role Playing Dimensions in Power BI

In this post I want to explain how to handle role playing dimensions in Power BI. I wrote an article awhile ago regarding role playing dimensions in SSAS Tabular which is valid for Power BI Desktop. But, in this post I show you two new alternative ways to handle role playing dimensions without importing tables, for instance DimDate,  into the Power BI model several times. You also don’t have to create database views on your source database. I show you how to manage this in both DirectQuery and Import modes when connecting Power BI Desktop to a SQL Server database.

I used AdventureWorksDW2016CTP3, but, you can use any other versions of AdventureWorksDW database or you can mimic the process to your own model.

Note: If you are designing a star schema for your data warehouse you can easily create a Date dimension as explained here.

The idea is to manage role playing dimensions in Power BI Desktop itself in the easiest way possible.

Role Playing Dimensions in Import Mode

  • Open Power BI Desktop
  • Get data
  • Select “SQL Server”
  • Enter the server and database names then click OK

Power BI SQL Server Connection

  • Select DimDate and FactInternetSales from the list then click “Load”
  • “Import” mode is selected by default. Click OK

Power BI Connection Settings

Continue reading Role Playing Dimensions in Power BI

How to Define A Measure Table in Power BI Desktop

In this post I show you a simple trick to make your Power BI model more organised and more readable. I call it creating a “Measure Table”. Let me explain. The story is that I was working on a model with lots of tables. The database schema was NOT a proper star schema so there were a bunch of measures spread into lots of different tables. On top of that we’ve created lots of calculated measures with different home tables which made it really hard to find a particular measure or calculated measure. I thought, well, when it is that hard to find the calculated measures at development time how hard it could be for a customer to find, understand and use the measures we created. The visibility of the calculated measures could be an issue when we have lots of measures in lots of different tables. You will soon feel the issue in customer training sessions when you need to navigate between lots of different tables to find a calculated measure.

Consider you create a Power BI model with direct connect to a SSAS Multidimensional instance. You will immediately notice that all measure groups have a special calculation icon (Measure Group Icon in Power BI) rather than a normal table icon (Table icon in Power BI) which makes the measure groups more recognisable for the end users. For instance, you can easily find any calculated measure related to “Internet Sales” under the “Internet Sales” measure group.

Measure Groups in SSAS Multidimensional Dirct Connect

I know, we can search and find the measures very easily, but, our model would be more organised and more user friendly if we can put all measures in one or more tables which contain just related calculated measures and nothing else. For instance, we can create a measure table for time intelligence calculations and name it “Sales Time Intelligence Measures” and put all  calculated measures like “Sales YTD”, “Sales LYTD”, “Sales Period Over Period” on it. It will make your model nice and clean, easy to use and easy to learn for your customers. It will also help you to train your customers more easily.

In this article I’ll connect to a SQL Server instance and will use the famous Adventure Works database. I also show you how to get the job done in both “Import” and “DirectQuery” modes as there are some limitations applied to the DirectQuery mode which makes it harder to do what we want.

Lets start.

Continue reading How to Define A Measure Table in Power BI Desktop

How to Disable Custom Visual in Power BI Desktop Model

Disclaimer: The method described in this post includes modifying Power BI file (PBIX) outside of Power BI Desktop. It is highly recommended to take a backup of your PBIX file beforehand. It’s highly probable that you corrupt your PBIX file if make a mistake when following the method described here. So please follow the process on YOUR OWN RISK!

Custom visuals are awesome. It’s easy to import them to Power BI Desktop model and start using them. But, what if you decide to remove them from your model? Is there a way to disable an imported custom visual?

Well, the answer is No and Yes! I mean, NO, there is no specific setting or option you can manage imported custom visuals in Power BI Desktop. But, YES, there is a way you can get rid of an existing custom visual. In this article I show you how to do the job.

First of all, I’d like to inform you that Microsoft will add the feature to disable custom visuals in Power BI Desktop, but, until then you can follow the my trick to completely disable/remove a custom visual from your Power BI Desktop model.

As you might already know a PBIX file is a compressed file indeed, so you can open it with a ZIP editor software like 7-Zip.


  • Download and install 7-Zip. It’s a free open source file archiver/compressor
  • Download and install Notepad++ which is also free and open source. It’s an awesome text editor

Removing/disabling Custom Visual

  • Open you Power BI Desktop model (PBIX file) containing a custom visual
  • As you see you need to enable custom visuals, click “Enable custom visuals”

Enable Custom Vizuals

  • I used “KPIStatusWithHistory” custom visual in my sample model

Custom Vizuals

Continue reading How to Disable Custom Visual in Power BI Desktop Model

Webinar Materials: Visualising Azure SQL DW with Power BI

Power BI Azure SQL DW PassIn the previous post I announced that I will speak in “Visualising Your Azure SQL Data Warehouse with Power BI” webinar on 23 Jan 2016. The webinar host was Pass Business Intelligence Virtual Chapter. It was such an amazing experience for me to speak in the webinar and I would like to thank all 105 attendees. The attendees showed their enthusiasm by asking lots of questions during the webinar.

In this webinar I demonstrated:

  • How to install Azure SQL DW in Azure Portal
  • How to configure firewall settings from Azure Portal and SQL Server Management Studio (SSMS) 2016
  • How to connect directly from Azure SQL DW to Power BI Service and the other way around
  • How to visualise you Azure SQL DW data warehouse data with Power BI Desktop (both Data Import and DirectQuery scenarios)
  • Comparing the features of different scenarios that helps you finding the best for your use cases

and much more…

You can see and download the session materials as follows.

Session Materials

Watch Visualising Your Azure SQL Data Warehouse with Power BI on YouTube

Continue reading Webinar Materials: Visualising Azure SQL DW with Power BI

Power BI Enterprise Gateway, Everything You Need to Know

Power BI Enterprise Gateway is release awhile ago (2 Dec. 2015), but, with the latest release on 22 Dec. 2015 Power BI Enterprise Gateway now supports live connections to both SQL Server Analysis Services Multidimensional and Tabular models as well as SAP HANA. In this post I’ll explain lots of important aspects of the Power BI Enterprise Gateway including installation,  configuration for different data sources including SSAS Multidimensional, Tabular and SQL Server Database and much more. If you need to have the lowest possible latency then you need DirectQuery/Explore Live feature on top of your on-premises data sources. The good news is that Power BI Enterprise Gateway now supports all following data sources:

  • SQL Server Database
  • SQL Server Analysis Services Multidimensional
  • SQL Server Analysis Services Tabular

    In this article you’ll learn how to install and configure Power BI Enterprise Gate Way, how to manage different live data sources, how to create reports on top of live data sources and more.

  • Note 1: If you want to use DirectQuery to connect to your on-prem SQL Server Database OR Explore Live your SQL Server Analysis Services Tabular model then you might not need to install and use the Power BI Enterprise Gateway. In those cases you can install Power BI Personal Gateway to connect to an instance of SQL Server OR install Power BI Analysis Services Connector to connect to your on-prem instance of SQL Server Analysis Services Tabular model rather than installing the Power BI Enterprise Gateway. But, bear in mind that selecting the best gateway is really depending on your use cases, your data sources and the environment you’re working on.

  • Note 2: The Power BI Enterprise Gateway and Power BI Personal Gateway CAN be installed on the same machine.

    Downloading and Installing Power BI Enterprise Gateway

    You can download the gateway from Power BI website when you logged in to your account and click on “Power BI Gateways” from the download menu:

    Downloading Power BI Enterprise Gateway

    OR you can go straight to the gateway page then download the Power BI Gateway – Enterprise (Preview):

    Direct Link to Download Power BI Enterprise Gateway

Continue reading Power BI Enterprise Gateway, Everything You Need to Know

Azure SQL Data Warehouse and Power BI

Azure SQL Data Warehouse and Power BI

Without a doubt cloud computing is going to change the future of data analytics and data visualisation very significantly. Microsoft Azure SQL Data Warehouse recently released for public preview. Combining Power BI as a powerful data visualisation tool with Azure SQL Data Warehouse will give the users the ability to see data insights of their data stored in Azure Data Warehouse very easily. In this post I explain how to install Azure SQL Data Warehouse and the the way it works with Power BI. Before going any further I’d like to have a look at the Azure SQL Data Warehouse very briefly.

What Is Azure SQL Data Warehouse?

Based on Microsoft documentation a SQL Data Warehouse is

Azure SQL Data Warehouse is an enterprise-class distributed database capable of processing petabyte volumes of relational and non-relational data.

Azure SQL Data Warehouse supports stored procedures, user-defined functions, indexes and collations. It uses columnstore index technology which significantly improves query performance as well as getting you up to 5 times compression in compare with traditional row based indexing.

I leave it to you learn more about Azure SQL Data Warehouse. But, it is important to keep in mind that there are some features like primary keys and foreign keys that are NOT supported in Azure SQL Data Warehouse which affect the way we use Power BI as a data visualisation tool over Azure SQL Data Warehouse. Without primary keys and foreign keys there is no physical relationships between the tables so Power BI service cannot detect any relationships by itself. There is a workaround for this that we can create some SQL views in Azure side to make it work. This can be an expensive solution. The other way is to load the data warehouse into a Power BI Desktop model which can detect the relationships automatically.

Now you know a bit bout Azure SQL Data Warehouse let’s get back to the subject and talk more about Power BI and Azure SQL Data Warehouse.

First things first. You need to have a Microsoft Azure subscription. If you don’t already have it you can use it for a one month trial here. You’ll also get $250 credit. But, remember that if you succeed the $250 in less than a month then you’ll need to pay for it if you want to use it longer.

Install Azure SQL Data Warehouse

After you get your Azure subscription, login to your account and you should see a dashboard like this

Install Azure SQL Data Warehouse

I’m not going to explain the above dashboard as it is out of scope of this article.

  • Click New

Install Azure SQL Data Warehouse 01

  • Click “Data + Storage” then click “SQL Data Ware House”

Install Azure SQL Data Warehouse 02

  • Enter a name for your database
  • Select a performance value

Continue reading Azure SQL Data Warehouse and Power BI