Power Pivot Principles: A Design for Life
13 March 2018
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:
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.
The ‘Insert Function’ option allows you to insert a new calculated column with a formula. The list of functions available are sorted by category.
‘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’.
‘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’ 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.
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.