Power Pivot Principles: A Design for Life

Welcome back to our Power Pivot blog series. Today, we consider some of the menu options in the Power Pivot window, focusing on the Design tab.

After covering the ‘Home’ tab in the previous blog, naturally we should move on to the ‘Design’ tab’s options. Let’s go ahead and click on the ‘Design’ tab.

Power Pivot Menu Options - Design

There are multiple sections made up of the ‘Design’ tab as follows:

Columns

The ‘Add’ option allows you to add in a new column and then type in a formula to create a calculated column (we will discuss these formulae – known as DAX formulae – and calculated columns later).  The ‘Delete’ option allows you to delete rows.  

The ‘Freeze’ option allows us to freeze the selected columns in place, while scrolling through the table. Do note that when a column is frozen, it will be moved to the very left of the table, and will remain there even after unfrozen.

The ‘Width’ option allows us to adjusted the width of the selected columns.

Calculations

The ‘Insert Function’ option allows you to insert a new calculated column with a formula. The list of functions available are sorted by category.

Calculation Options

‘Auto Calculation Mode’ should be set to ensure that the formulae (measures) and calculated columns are calculated automatically.  If we have a large data model with many computations, we can change this section to be ‘Manual Calculation Mode’ so that the results will not calculate until we select ‘Calculate Now’.

Relationships

‘Create Relationships’ allows us to create relationships between your tables of data.  To create, edit or delete a relationship select ‘Manage Relationships’.  We have previously covered this topic in this blog.

Table Properties

‘Table Properties’ allows us to work with the data in our data model.  We can change the column, row or table mappings.  If we brought in columns of data in the data model that we don’t need this would be one method to remove those columns.

Mark as Date Table

This option allows us to mark the selected table as a date table to enable dedicated date filtering in reports that you create in applications like Excel.  This requires one of the fields to be a date field.  These dates must cover all dates in the database, contain no duplicates or missing periods.

Edit

The ‘Undo’ and ‘Redo’ options allows us to undo and redo the last action performed in Power Pivot similar to the Excel functionality.

That’s it for this week, stay tuned for our next post on Power Pivot.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.