Deriving Columns from What You Know in Power BI

Before I get started with the topic of the day, I want to remind you that Power BI is still being updated by Microsoft. In fact, there was an update just last week on October 20th that appears to have added some missing features that I mentioned before. So if you haven’t updated Power BI recently, be sure to do that before reading the rest of this blog.

A few weeks ago when I was looking at defining relations between tables in the Relationship dialog I was disappointed in the fact that when I display my tables in what I would have previously called a Dialog view in PowerPivot, I could not use drag and drop to define my relations. Well, my disappointment is over. The latest update, among other things, adds this capability.

Since last Wednesday, October 21, was ‘Back to the Future’ day, I want to go back to last week’s blog just after I added the DimProductCategory table. This time however, rather than using the Management Relationships dialog, I am just going to the Relationships view and drag the field ProductCategoryLabel to ProductCategoryKey as shown in the following image.

Now I must say that I am use to just identifying the fields from my two tables that I want to connect to form the relationship and expect PowerPivot to figure out which is the one and which is the many table. Unfortunately, Power Bi still is not quite this smart. Because when I attempt to define this relationship from the one site to the many side, the relationship definition fails as shown in the following figure.

Maybe the next update will automatically reverse the direction of the relationship for me. However, for now I can click the OK button in the message box shown above and then redefine the relationship correctly from the DimProductSubcategory to the DimProductCategory table.

Now the relation is created and I am ready to continue. By the way, when I click on the relationship, Power BI highlights the two tables as well as the relationship line. It also encloses the connecting fields in a box to make it easy to identify the relationship fields.

If instead of left clicking on the relationship, suppose I right click on the relationship. Now I get an expected option menu which in this case consists of only a single option, Delete. Clicking this option will of course delete the relationship.

On the other hand, double clicking on the relationship opens the Edit Relationship dialog shown below. I can use this dialog to view the relationship or a sampling of the data, or make changes to the relationship.

That’s great. But like an infomercial, there’s more! In the Relationship view (Diagram view) I can also now right click on a field and delete a field, hide a field from the Report view or rename the field.

As with PowerPivot, if I delete a field, it is gone for good. If I realize later that I need the field, I would need to delete the table and reload it to get the missing field. Of course there are other consequences to doing this like needing to redefine the relationships and possibly rebuilding reports (visualizations) that used that table. Because at this time, I cannot visually define the fields to include or exclude during my original data load from my data sources, it is important that I know my data and delete any fields that I know that I will not need before I begin creating new columns, measures, or reports. In PowerPivot, we called these columns Useless columns.

I can also hide some columns from the Report view. For example, I can often hide columns used to define relationships between tables because end users typically do not include these columns in reports. Hidden columns are referred to as Technical columns because they are required in the data model and cannot be deleted without destroying relationships or perhaps calculations of other fields. I never want to show more columns to a user than they know what to do with.

Finally, renaming columns can be very beneficial. Often column names in databases have cryptic or abbreviated names. End users may not be comfortable with these shorten names. Use the Rename feature to make names user-friendly and descriptive.

Not only can I delete, hide, or rename columns in a table, but by right clicking on the table header, I can perform these same actions on an entire table. For example, I may not like dimension tables that begin with the letters ‘Dim’ like many DBAs prefer but end users may have no idea why the table is Dim. Similar to columns, I don’t delete tables from my model unless I am positive that I do not need them. Hiding tables only makes sense if I want to use only one or two fields from a table. In this case, I may need a column from another table for a calculation, but I would never display those columns directly in reports.

Returning to the data view of my tables, I can also right click on any of the column headers and delete, hide or rename the column. There are also several other options ranging from sorting, to creating new columns or new measures.

I can also right click on the column names in the fields list along the right side of the Data view. The dropdown list of options shown below is similar to options in the context menu above. So I have several different ways to manage columns

Let’s try something new, a new column in fact. In my FactSales table, I can find several columns like SalesAmount, TotalCost, and several others, but there is not Total Profit column. I can easily calculate that value from other values in the table. To begin, I need to create a new column in my data model. That means clicking the New Column button in the Modeling ribbon.

New columns are created at the right end of the table. By default, the column name is cleverly called Column. Of course I can change that by entering a new name. Then after an equal sign which indicates that an expression will be used to define the column, I can begin entering the column definition using DAX expressions. Yes, DAX is still alive and well. If you need a review of using DAX, I’ve covered multiple DAX topics over the last couple of years of this blog.

As in PowerPivot, I can select a column from the current table by just typing the left square bracket. This action opens a dropdown of all column names in the current table listed alphabetically. I can scroll down through the list and select a column by double clicking on its name. I can also type a few characters of the column name to narrow down my list as shown below.

My full expression to calculate TotalProfit is shown below

When I click the Enter key, the entered expression is used to calculate the values for all the table rows.

Before moving off the column, I might want to define custom formatting for the values. The formatting definition here is carried forward to all reports generated with the data. In the above example, I might want to only display the dollar amounts to two decimal places. (Actually, because of the size of aggregated data, I might later decide to format the values with no decimal places.)

Suppose that I want to display some of this data using a standard table with Channel names for the rows and a few select columns from the table. Notice that the values displayed here obey the formatting definition set on the Data page.

That’s it for this week. Next time I cover creating New Measures and why you might want to do so.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s