Tip #1: Easily Copy Your Data Model from One File to Another
Let’s say we’re working with an Excel file that has the following configuration:
- We have brought in a series of data tables using Power Query.
- We have created a Data Model with relationships between the tables.
- We have created a series of DAX Measures and KPIs to assist with calculations.
We want to copy all these facets of our Excel file and paste them into a new Excel file so we can take our analysis in a different direction.
You could try to copy and paste each of these elements separately, but you would still be faced with manually reconstructing many parts of the process.
The trick to transferring everything in its entirety is to create a tiny “dummy” Pivot Table.
If we create a Pivot Table with at least one field from each table in the Data Model, we will analytically connect every element of the modeling process.
The steps are as follows:
- Start a new worksheet and insert a new Pivot Table using the data from the Data Model as the source.
- Insert at least 1 field from each table of the Data Model into the Pivot Table.
We have created our “dummy “ Pivot Table.
- Highlight the entire Pivot Table and click Copy (CTRL-C).
- Start a new workbook and click Paste (CTRL-V).
Everything from the original workbook that contributed to the construction of the copied Pivot Table is now contained in the new workbook; Power Query queries, Data Model, table relationships, Measures, KPIs, etc.
Four simple steps to save potentially hours’ worth of reconstruction.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Tip #2: Dedicated Measures Table
When working with DAX Measures in Power Pivot, the Measures can be placed in any table in the Data Model as they are independent calculations.
Placement preference varies from user to user.
Some users like to place the Measures in the table they are most focused on analytically, while others place all measures in a single table.
The default table to place all new measures is the table that entered the Data Model first.
This destination table for Measures can be changed during the Measure creation process by clicking the dropdown for the “Table Name” option and selecting the desired table.
If you are placing all your Measures in a single table and that table was not the first table placed in the Data Model, reselecting the Measure destination for each new Measure can become tedious.
Of course, if you know which table you want to store all your Measures in, just bring that table into the Model first and all this is a non-issue.
Creating a Dedicated Measures Table
However, some users prefer to have ALL measures stored in a dedicated “Measures Table” that has no other data.
Here’s how we can create such a table.
NOTE: The creation of this table must be done BEFORE any other tables are brought into the Data Model.
- Start a new workbook and copy cell A1. (the cell is empty)
- Open the Data Model by selecting Data (tab) -> Data Tools (group) -> Data Model.
- In the Power Pivot window, click Paste.
- In the Paste Preview window, give the new table a name such as “KPI” and click OK.
- To create an attractive measures icon in the Pivot Tables Fields List, right-click on the new empty column’s heading and select “Hide from Client Tools”.
- Close the Power Pivot window and import the remaining data into the Data Model through whatever means you prefer (Power Query, direct import, etc.)
Now, when we create our Measures, the default landing table for new Measures is the empty table we create in Step 4.
When you create a Pivot Table from the Data Model, you will see the Measures table with an attractive SUM symbol beside the table name. This was created when we hid the empty column from the Client Tools.
NOTE: Placing all Measures in a single “Measures Table” looks nice and makes locating Measures easier during workbook maintenance. However, this comes at a cost. If you are using Web Services such as “Q&A” or “Quick Insights”, you may run into ambiguity issues due to the distances of objects in the Data Model. If you are not using such services, a single “Measures Table” may serve you well.
Featured Course
Power Excel Bundle
Tip #3: Use Cube Functions for Flexible Reporting
When working with a Pivot Table, you may wish to reference a specific value in the Pivot Table separately, perhaps as part of a dashboard report.
A simple way to achieve this is to use the GETPIVOTDATA function. This only requires typing an equals sign then clicking on the cell of the Pivot Table that holds the desired value.
The result is as follows:
This is of great benefit because if the table expands, contracts, or changes structure, as long as the [Quantity] field remains in the Pivot Table, the aggregation will be returned.
Although simple and easy, this has a huge weakness.
If the Pivot Table’s aggregation field changes (ex: from [Quantity] to [Median Quantity]), the GETPIVOTDATA function fails.
How can we show the value of the desired aggregation as an independent result that is not dependent on the Pivot Table?
This is where CUBE functions can be of benefit.
Don’t stop reading! If you have never used a CUBE function before, or they seem scary, you can benefit from them with very limited knowledge of their inner workings. Just follow these simple steps:
- Create a Pivot Table that contains the values you want in your dashboard. For our example, we just want a total quantity.
- With the new Pivot Table selected, convert the Pivot Table to a set of standalone functions by selecting Pivot Table Analyze (tab) -> Calculations (group) -> OLAP Tools -> Convert to Formulas.
- The two cells making up the Pivot Table have been replaced with CUBE functions.
In our above example, because the CUBE function in cell D5 is dependent on the result of the CUBE function in cell D4, we can’t simply copy the D5 formula and discard the D4 formula.
We can however combine the two functions into a single function by placing the entire CUBEMEMBER function in place of the D4 cell reference in the CUBEVALUE function.
If you are familiar with CUBE functions, you know that we can shorten the function by removing the “CUBEMEMBER(“ThisWorkbookDataModel”,” portion of the formula without any issues.
The result of the CUBE function will refresh whenever the Data Model is refreshed.
Customizing the CUBE Result’s Formatting
To make the CUBE function’s output more presentable, we can concatenate a label to the front of the CUBEVALUE function.
(click above formula for larger image)
We can also use the TEXT function to apply formulaic formatting to the result of the CUBEVALUE function.
(click above formula for larger image)
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
