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:
But this is what I got:
“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())
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:
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.
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:
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:
Apparently the category is treated as secondary to the data type. Therefore, one must type latitude and longitude fields as Decimal Number.
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:
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:
And in the following illustration, the filled map acts as a filter on the table and the bar chart:
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:
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:
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.
After using the brand-spanking-new Power BI Desktop for a couple of days, these are my favorite features so far, especially as compared to Power View and PowerPivot:
- Ease of navigation between data model and visualizations. No more switching between windows.
- One-click publishing to powerbi.com. It couldn’t be easier.
- The new visualization types and the potential market for third-party visualizations.
- The many (and growing) list of data sources.
- More control over chart customization (e.g., colors, titles, etc.). For many people this was a reason for looking at other products and a let down when compared with Excel charts.
- Seamless integration between data, data transformations, and data model. Again, no more switching between windows.
- The unity of interface between the Power BI Desktop and the report editor in powerbi.com.
- The placing of measures in the fields list of a table. I could never get my measures to look right at the bottom of the grid and was always playing with the column widths. Now measures are first-class citizens.
- The elimination of the colon for defining measures. Perhaps a minor detail, but syntax simplification and uniformity is always welcome.
- The ability to connect to and build reports from tabular models without having to import data. Another huge reason to not look at other products.
- The promise of frequent updates and improvements. This should keep the excitement going.
- The fact that the community’s input is actively being sought and paid attention to. It’s great when you submit a smile or a frown and you get a response for more information.
What’s still in Power View and PowerPivot that I soon wish to see in the Power BI Desktop, hopefully in an improved fashion:
- The play axis for scatter charts.
- More control over fonts.
- Hierarchies in the data model.
- Column filters in the data view.
- Default aggregations for fields.
- Synonyms for Q&A.