Column Sub Folders are Better than Measure Sub Folders - Excelerator BI

Column Sub Folders are Better than Measure Sub Folders

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.  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

Benefits of creating a column sub folder instead of a measures sub folder 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 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).

34 thoughts on “Column Sub Folders are Better than Measure Sub Folders”

  1. Hello Matt,
    I was just tring to do sth new and found your blog. It solves many practical issues.
    After creating the column or measure sub-folders, if I create any Column or measure that doesn’t comes under the sub-folders. Can you please help me with this.

  2. I can no longer move my REPORT LEVEL Measures between tables inside a Live Connection. I believe this all came about when Power BI updated all the options being discussed above.

    Import mode has more options to move and group your Measures. But Report Level Measures. I’m stuck at the moment. If I want to move them, I have to delete them and re create them inside the table I want them to be in.

    Has anybody else had this experience, and know a workaround?

    1. Matt Allington

      I just tested it with the May 2019 version, and it works for me. Drag and drop measures works in the Model view, and also I can select a measure in the report view, click modelling and change the home table

      1. I don’t understand how you can get to the model view with a live connection? Are you understanding that I am asking about Report Level Measures when you have a live connection to the Power BI service? Not regular measures.

  3. I can no longer move my REPORT LEVEL Measures between tables inside a Live Connection. I believe this all came about when Power BI updated all the options being discussed above.

    Import mode has more options to move and group your Measures. But Report Level Measures. I’m stuck at the moment. If I want to move them, I have to delete them and re create them inside the table I want them to be in.

    Has anybody else had this experience, and know a workaround?

    1. Matt Allington

      I just tested it with the May 2019 version, and it works for me. Drag and drop measures works in the Model view, and also I can select a measure in the report view, click modelling and change the home table

      1. I don’t understand how you can get to the model view with a live connection? Are you understanding that I am asking about Report Level Measures when you have a live connection to the Power BI service? Not regular measures.

  4. My first thought was that this was brilliant. But then I realized that in the example above (looking at the sales/fact table), the none-fact columns should have been hidden anyway, and you do want to mix the remaining fact column (if not replaced with explicit measures) with the measures to get a good overview. So if we hide what should be hidden and make sure to not enable “view hidden”, all the benefits kind of goes away. Although, having said this, it’s still something appealing with your suggestion Matt

  5. My first thought was that this was brilliant. But then I realized that in the example above (looking at the sales/fact table), the none-fact columns should have been hidden anyway, and you do want to mix the remaining fact column (if not replaced with explicit measures) with the measures to get a good overview. So if we hide what should be hidden and make sure to not enable “view hidden”, all the benefits kind of goes away. Although, having said this, it’s still something appealing with your suggestion Matt

  6. Very nice. It would be even nicer if Microsoft provided a way to put all measures in a folder as well. Then we would have a folder for columns and one for measures. And we wouldn’t need to preface the Columns folder with an underscore.

  7. Very nice. It would be even nicer if Microsoft provided a way to put all measures in a folder as well. Then we would have a folder for columns and one for measures. And we wouldn’t need to preface the Columns folder with an underscore.

  8. Thanks Matt.
    What a real time saver. This makes it a lot easier for me to see and find out what I want without having to scroll down and up looking for the measure I need.

  9. Thanks Matt.
    What a real time saver. This makes it a lot easier for me to see and find out what I want without having to scroll down and up looking for the measure I need.

  10. Nathan Brunner

    I like it! What happens when you add an extra column sometime in the future? Do you just have to manually add it then to the sub folder

    1. You are right Nathan. New columns need to be added to the folder, but new columns are not as common as new measures, particularly if you avoid writing calculate columns where possible (as I always advise)

  11. Nathan Brunner

    I like it! What happens when you add an extra column sometime in the future? Do you just have to manually add it then to the sub folder

    1. You are right Nathan. New columns need to be added to the folder, but new columns are not as common as new measures, particularly if you avoid writing calculate columns where possible (as I always advise)

  12. As I have learnt, there are many ways to skin a cat. But this is by far, the best way to do so. Thanks.

  13. As I have learnt, there are many ways to skin a cat. But this is by far, the best way to do so. Thanks.

  14. I’ve been searching for the best solution for storing measures since I started using Power BI 2 years ago.
    Until Microsoft allow us to create a ‘logical’ model which doesn’t break things like Q&A, this is by far my favorite approach. Great work Matt – thanks!

  15. I’ve been searching for the best solution for storing measures since I started using Power BI 2 years ago.
    Until Microsoft allow us to create a ‘logical’ model which doesn’t break things like Q&A, this is by far my favorite approach. Great work Matt – thanks!

  16. Jim Gutherson

    Thank Matt. This was just in time information. I was just thinking about a way to do this when your email arrived. Perfect

  17. Jim Gutherson

    Thank Matt. This was just in time information. I was just thinking about a way to do this when your email arrived. Perfect

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top