Skip to content

Pasted tables–a poor woman’s display folder

February 9, 2012

One limitation of the tabular model is that display folders are not supported. Another limitation of the tabular model is that you cannot add simple aggregation measures (sum, average, etc) on the fly in pivot tables in Excel. Because all aggregations must be pre-defined in your model, the number of measures in your fact table quickly proliferates. Since neither Power View nor Excel support search in the field list for tabular models, it rapidly becomes hard to find measures in the field list when there are many different facts in your model, and the field list becomes unusable. What to do?

One thing you can do is use empty tables to hold related measures. Measures can be placed on any table in the tabular model with no ill effect. The only difference between placing the measure directly on the fact table vs. placing the measure elsewhere is how you reference the columns and measures in the calculation – names must be fully qualified if the measure is outside the fact table. Personally, I find that a small price to pay for a more organized field list.

You can easily construct an empty table using pasted tables. This works for all models except DirectQuery models, which do not support pasted data. Here is how to do it.

Step 1: create some dummy data for the empty table’s column. This is easy enough to do in Excel, a 1×1 piece of data will do:

image

Step 2: copy this data. If you are working in SSDT, go back to the SSDT designer window and click inside the designer. Then go to the Edit Menu, select Paste:

image

Step 3: The Paste Preview dialog box appears. Name your “display folder”, ensure the “Use first row as column headers” box is checked, and click OK.

image

Step 4: To ensure this dummy column is not visible to end users, hide it by right-clicking the column and selecting “Hide from Client tools”

Step 5: Add measures. One thing to note: although copy and paste across cells was enabled in the measure grid, it only works on cells in the same table. You cannot right click a cell in the measure grid, copy it, then paste it in another table. If you are migrating measures from your fact table to the new table, you will have to select the text of the measure in the formula bar, copy the text, and paste the text into the formula bar in the new table. Make sure you fully qualify any column or measure names when you move the measures from table to table. You might find it easier to use the DAX Editor to move the measures from place to place instead.

Step 6: Open Excel or Power View. Enjoy your newly organized measures.

Advertisements
One Comment
  1. Hans van L permalink

    Cathy, in fact there does seem to be one ill effect: drill-down works on the table that a measure is defined in. In an Excel pivot table, if I double-click a measure that I have placed in my dummy table, then I get the rows in the dummy table itself. For me, this is a show-stopper. This is unfortunate because I thought it was a neat idea.

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

%d bloggers like this: