Column Sub Folders are better for grouping measures in Power BI

I blogged about the various ways to use Measure tables and Measure Sub Folders in Power BI a few weeks back.  I discussed why measure tables may be required, and also a few reasons why you may choose not to use a measure table. I also mentioned some alternatives to using measure tables, including using a sub folder for the measures inside the main data table. In this new blog article today, I will show you another (preferred?) way of grouping measures in Power BI.  Instead of using measure sub folders, I am going to suggest that you use column sub folders.

Remember, you heard it here first 🙂

The Method

• When you write your measures, place them in the table where the data comes from, so that each of the tables will contain its own relevant measures.
• Then, in each table where you have written one or more measures, create a sub folder for all the columns in that table.
• Give a suitable name to the sub folder so that it floats to the top of the fields list.

Benefits of this grouping method

Benefits of creating a column sub folder instead of a measures sub folder for grouping measures in Power BI include:

• The sub folders are collapsed by default.  All the columns will not be visible by default unless you expand the column sub folder.
• All the measures (what you want people to use) will be visible by default without having to expand a sub folder.
• All the measures are visible together in a single list, not separated by interspersed column names.
• When you write new measures, they will automatically appear in the list of other measures – no need to separately add them to the measures sub folder later (this doesn’t happen by default and becomes a second step).
• The measures are kept in the table they are created from and hence this improves the results coming from Q&A (see my earlier blog linked above for more details on this).

Example

I will illustrate this grouping method using the Adventure Works database.

As you can see below, the measures in the Sales table are scattered in the fields list. Our objective is to display the measures together so they are easy to find and use.

Create a Folder in the Sales Table for Columns

In the Model view, click on ellipses (#1 below) and then click on Select columns (#2 below).

Next, in the Properties window (#1 below) give a name to the Display folder (#2 below). You can see that all the columns in the Sales table are now grouped under the newly created sub folder (#3 below).

Switch over to Report view and you can see all the columns have been moved to the _Columns sub folder.  Now all the measures are all visible together in one place in the table (#1 below). The columns are still there, but they are stored in the sub folder (#2 below).

Edit April 2020: With the integration of Tabular Editor with Power BI Desktop, it is now possible to move all columns into a Display Folder with Tabular Editor. You can find how to do it in my article here.