Power BI Best Practice: Use Dedicated Views and Schema to Fetch Data

If getting data from a relational source such as SQL Server, use views to abstract the report from the physical layout of database tables and never write queries inside reports; from a maintenance and development standpoint, it is simpler and faster to update a view than it is to edit a query inside a report and then republish the report.

Also, if available, create a separate schema for the views for grouping and security purposes.  This is the recommended architecture:

Click here for a full set of Power BI best practices.

Carefully Choose the Data Source Connection Method in Power BI

There are three ways to connect to data: Import, Live Connection, and DirectQuery (note: currently in preview are hybrid methods).  The choice of method will depend on various considerations, such as the size of the data, the number of users, and the data refresh needs.  Make sure an appropriate assessment is made before the report is built, as switching from one method to another is not always possible or straightforward.

The following chart summarizes the options on several dimensions.


*This is for Power BI Pro.  Power BI Premium has more flexible refresh and size limits.

Click here for a full set of Power BI best practices.

Setting Up Drillthrough Filters in Power BI

[8 September 2017]  This feature is now part of the September release of Power BI Desktop.

A recent addition in the Power BI Service is the ability to define “Drillthrough Filters” in a report page.  You can think of these as dataset fields that serve as parameters to the report page.  Let’s call such a page a “drillthrough page.”  You can navigate to a drillthrough page from another page by right-clicking on a visual that includes one of the drillthrough fields.

Set Up

For example, I have a report about sales and collections that includes salespeople and their supervisors.  One of the report’s pages, called Totals, breaks down sales and collections by salesperson and allows filtering by month/year and supervisor.


I will use this page as a drillthrough page with Supervisor as a drillthrough filter by dragging the Supervisor field on to the new “Drillthrough filters” field well for the report page:


Once you complete this action, a new shape visual (an arrow) will be added to top-left corner of the page.  This will serve as a “go back” button when navigating by clicking on it while pressing the Control key:


The visual is hard to see in this case but you can move it around and even edit it, just like any other shape visual:


And note the new “Back button” option for the visual:


In fact, this option is now available for all shape visuals and clicking on it will get you back to the previous page, regardless of drill through.

Drilling Through

If I go to a visual on another page that includes the Supervisor field I’ll get a new option when I right-click on an item:



When you drill through, you will see that the Drillthrough filters field well now has the corresponding value selected as a filter.  It can be modified and cleared, too:


Finally, a page may have more than one drillthrough filter and a field may be a drillthrough filter for more than one page.  For the latter case, you would get multiple options on right-clicking:


Overlaying an Image to get a Proportional Fill Effect in Power BI

In a super cool trick for Power BI, Miguel Myers (@myersmiguel) did an overlay between a bar chart and a duplicated image to simulate a proportional fill, resulting in this:

base example

You can see the full report here.  While he shows how to do it in a YouTube video, it took me a while to simulate it and I thought it would be nice to write it up.

To start, you will need an image to work with.  This is my initial image:

Original Image

Now make the outside of the silhoutte the same color as the background of your report.  In my case, it’s already white so I didn’t have to do anything (in Mykhael’s report, it is black).

Next, make the shape transparent:

One Image Photo

And then duplicate it as many times as the maximum for your measure, as in:

Many Images Photo

For example, for a measure that’s a percentage, you may want to have 10 shapes so that each represents 10%.

Now to the report.  Create a bar chart and remove the X axis and the title:

Clean Chart

Bring in your image over it and immediately you will start seeing the end result:

Iniial Overlay

Adjust the heights of the bar and of the image so that the bar is fully covered:

Adjusting Height

Then left-align the image to the start of the bar

Final View

So that the bar does not stretch past the image, you will need to make sure that the image is exactly and completely filled at the max value of the measure.  This will also take care of displaying the right amount of fill for the different values of the measure.

Another key point is to set the start and end values of the axis so that they don’t change with different filter selections in your report and then wreak havok on the proportions you just coordinated.

This is the final product:




Using a Power BI Scatter Chart to Visualize Control of Congress

In this post I will show you how I built this pretty chart that depicts which party controled each house of the United States Congress (the Senate and the House of Representatives) between 1968 and 1980:

Final Chart

The chart displays, per year, the party that controls each house and the relative majority under which control is held.  The first item is given by the color of the dots and the second by their size.  Hovering over any of the dots shows this:


I got the raw data from Wikipedia, and it looks like this:


I had to format the data in a special way to build the chart, especially for the year-by-year continuity.  This is because elections are not held every year but are staggered.  In the Senate, seats are held for six years but elections occur every two years for approximately one-third of the seats.  In the House, elections are held every two years for all members.  So after some M programming and a bit of DAX I got it into this shape:


To build the scatter chart, we need two measures, one for the X axis and one for the Y axis.  For the X axis, I used the Begin Year and for the Y axis the House Type (1 for Senate, 2 for House).   Using Controlling Party for the legend provided the colors and the size was given by the Majority Percentage.  This is the final configuration:


The last touch was to use an overlaid text box to label the final Y axis:

Y Axis

As a final note, the Quick Insights feature of Power BI gave me the idea for this chart:


View the complete report here.

Power BI Fun with a Baby Names Database

The United States’ Social Security Administration publishes counts of the names of newborns.  Kendra Little has loaded these counts for the years 1880 to 2014 (which are provided in separate files per year) into a SQL Server database that she has made available here for download.  I had some Power BI fun with it exploring the most common names throughout the years.

Word cloud for the top 10 female names between 1900 and 1949:

Female Names

Sample of animation for the top 5 male names from 1880 to 2014:
Baby Names

Download the Power BI Desktop file here (6MB).  The Power BI version is 2.30.4246.181 64-bit (December 2015).  It uses version 1.0.2 the WordCloud custom visual available in the Power BI Visuals Gallery.  For a live version of the report, scroll to the end of this article.  It was embedded using the new “publish to web” feature in Power BI.

The visuals use a ranking number assigned per year, per gender.  My first version calculated the rank in DAX using RANKX but the performance was poor, especially for the animated scatter chart; it took a long time to load and refresh.  I solved the problem by calculating the rank in T-SQL like this:

SELECT a.[FirstName] as [First Name]
 , [ReportYear] as [Report Year]
 , [Gender]
 , [NameCount] as [Name Count]
 , DENSE_RANK() OVER (PARTITION BY [ReportYear],[Gender] ORDER BY [NameCount] DESC) as Ranking
 FROM [ref].[FirstName] a inner join [agg].[FirstNameByYear] b
 on a.FirstNameId = b.FirstNameId;

Table agg.FirstNamesByYear has about 1.8 million records.

One Solution to the “missing intermediate data” Error on Power BI Relationships

One of the great new features in the September 2015 release of the Power BI Desktop is calculated tables.  I recently used it to create a table of pharmaceutical product groups, because the group names where present in the Product table but not in a column with unique values; therefore, I could not use the column on the one-side of a 1-many relationship.  I created the table Product Group with this formula:

Product Group =

I then tried to create a relationship between the resulting column, which I renamed “Group”, and the column “Brand” on the Speaker Event table:

Try Create Relationship

But this is what I got:

Error Message

“Missing intermediate data”?  After a while, I discovered that at the very end of the Group column there was a blank value.  Removing it resolved the error.  I did this by changing the calculated table’s formula to this:

Product Group = FILTER(DISTINCT(VALUES('Products'[Group])),'Products'[Group] <> BLANK())


Resolving Unexpected Behaviors with Latitudes and Longitudes in Power View and the Power BI Desktop

A couple of months back I wanted to do a simple map showing the location of health centers in Puerto Rico based on latitudes and longitudes, using Power View and PowerPivot.  However, Power View insisted that all of the locations where off the west coast of Africa!  After battling back and forth for a while and triple-checking the values directly in Bing Maps and in Google Maps, I discovered that the data types of the latitude and longitude fields had to be Decimal Number.  Look at the before and after results:


I would have quickly discovered the need for the Decimal Number data type if I had tried to categorize the fields as Latitude and Longitude (respectively) in PowerPivot, as it gives this warning:

Category and Data Type Warning

The categorization didn’t initiallly occur to me as I was telling the map chart exactly what fields to use for Latitude and Longitude; I thought that was enough indication.

Chart Spec

The Situation in the Power BI Desktop

In the Power BI Desktop the data type expectation is immediately obvious because if the fields are text, it will force a COUNT aggregation:

Count of Latitude

This can be noticed as soon as the field is dragged to the box, but we cannot get rid of the COUNT because it lacks the familiar “Do no summarize” option that we have in Power View.  Moreover, categorizing the field (as shown below) does not resolve the issue either:

PBID Latitude as Text and Categorized

Apparently the category is treated as secondary to the data type.  Therefore, one must type latitude and longitude fields as Decimal Number.

Power BI Desktop Tip: Use Identical Maps for Simultaneous High- and Low-Level Views

Whenever working with anything geographical, maps are a cool visualization.  One of the new visualization types in Power BI is the filled map, in which regions may be colored rather than just pinpointed.  For example:

Map and Filled Map

And like regular maps, filled maps may work as filter sources as well as filter targets.  In this next image, the bar chart acts as a filter on the table and the filled map:

Filter 2

And in the following illustration, the filled map acts as a filter on the table and the bar chart:

Filter 1

And now the tip.  If you copy the filled map, you get one to work as a filter on the other and therefore create simultaneous high-level and low-level views of the selected area:Animation

This of course works with a filled map filtering a regular map and vice versa.

You can expand this to another level of geographical detail, as in this image with US counties, in which the state of Tennesse is selected in the top map:

State Region County

In a future post I will work through this last example step by step.  It uses diverging coloring with custom minimum and maximum population values to color the top map, and ranking in DAX to display the top 10 most populated counties.

Get a sample Power BI Desktop file here.