Recently while working with a customer I was presented with a challenge that I hadn’t considered before; they have more than 50 measures in a dataset and asked for the best practices around organizing them. After pausing for a bit too long, I said I would get back to them and provide our recommendations. I then proceeded to spend the better part of 2 days agonizing over how to make a simple yet robust system for organizing measures for both dataset developers and report builders.

This blog consists of two main sections, the first being a couple of tips that are easy to start using and will get your datasets organized with minimal time spent, the second section will be taking a deep dive into my organization structure and the reasoning behind it. 

So, without any further ado, let’s get into.

The first thing that everyone should be doing once they have more than a couple of measures in their dataset is create a measure table. And if you take nothing else from this blog post, it should be that measure tables are key for having a well-organized dataset.

A measure table is a way that Power BI interprets a table that has measures but no columns. So what does that actually mean, and how do you make one?

I think that this is best explained by making one, so let’s do that!

With how important they are, they are very simple to make: Go through the “enter data” dialogue and don’t enter any data (I have given this table a name of “Measure Table” but you can name yours whatever your heart desires):

Once you have the new table in your dataset, hide the only column:

Then start moving your measures to it (this can also be done by dragging and dropping them in the model view):

Once you save and reopen your file Power BI will recognize what you are doing and show a different icon for the table, as well as put it at the top of the list in the fields section:

This is where most use cases end, and for every dataset with 10 or so measures, the effort involved in the next steps is probably not worth it. But for those with dragons to slay, I present to you DISPLAY FOLDERS.

Display folders were rolled out quite some time ago now but I had not used them until recently. They allow you to create structures within tables to organize measures. Much like measure tables, these are very easy to start using, but they do come with their own set of annoyances.

To create a display folder, navigate to the model tab on the left side of Power BI Desktop:

Then, from the fields section, select a measure and look at the properties pane:

Here you will find the “Display Folder” text box. To put a measure in a folder, just type the name into it:

This is where one of the biggest annoyances of working with display folders lies, there is no ability to select from the folders that already exist in the table. However, it is not all doom and gloom because we can drag and drop measures into existing folders. Additionally, there is actually a feature I didn’t know about with display folders, which is the ability to use sub-folders. When typing in the “Display Folder” box, use “\” to create sub-folders:

This is the key to my organization methodology.

We now wade deeper into the waters with:

With the problem stated and the solution’s foundation laid, let's get into the organization methodology. First up, I create a folder “KPIs” that contains all of the base measures that are used commonly in reports driven off the dataset. By base measures, I mean measures that will be used in YoY, Variance to budget, and other time/business logic measures. I then create as many of the following folders as needed, depending on the use case:

• Time
• Totals
• Variance
• Labels
• Counts

I’m sure that these are not a comprehensive list of categories that could be needed, but they do offer a good jumping-off point for organizing your own files.

Let's talk specifics on these other folders.

Now, with over 1000 words about organization, we reach our...

As mentioned in the intro to this blog, this all came about because of a dataset that had a bunch of measures in it (I’m sure that tabular model or AS cube developers are scoffing at 50) and needing a way to make them easily usable without having to constantly use the search bar or an external data dictionary. This may seem a bit overwhelming to implement all at once, but it’s not necessary to implement all of this all of the time. You might only need to create a measure table or create a couple of display folders. Hopefully, this blog has helped you determine what you need for your datasets. And while I think that this pattern is fairly robust, you will most likely need to modify it to your given analytics needs. My main objective is to give you a framework to start organizing your datasets and think about their usage.

Speaking of modifying this pattern, my main advice would be to try and boil down the primary usage of the measure being created and categorizing based on that rather than organizing based on source. This will make your dataset easier to use for developers creating reports to find things.

And with that, we have gone from having measures all over the place to creating a measure table and folders, and talking through my recommendations for how to structure those folders.