Setting Display Folders in Analysis Services Tabular 2016

By:   |   Comments (4)   |   Related: > Analysis Services Development


Problem

I have a large Tabular model created in Analysis Services 2016. In most tables I have many different measures or columns and it’s becoming really hard for the users to navigate the model. Is there any way I can group objects together?

Solution

Analysis Services Multidimensional has the solution to this problem for years: display folders. You can assign measures or dimensions attributes to a specific folder which makes browsing the model more user friendly. An example from the AdventureWorks sample cube:

display folders in SSAS Multidimensional

Analysis Services Tabular 2012/2014 didn’t officially support these display folders. However, you could add them by either manipulating the XMLA code of the model directly or by using BIDSHelper.

Luckily Analysis Services 2016 now officially supports display folders, which mean you can configure them easily through the Visual Studio (SQL Server Data Tools) interface. In this tip, we’ll give an overview of this new feature.

Setting the Display Folder

Let’s set the display folder for some measures in the WideWorldImporters Tabular model (see the tip Using Calculated Tables in Analysis Services Tabular 2016 for Aggregate Tables – Part 3 for more info). When you select one or more columns, you can set the Display Folder in the property pane:

Set the display folder in Analysis Services Tabular 2016

Setting the display folder for a measure is exactly the same. When we browse the model with Excel, we can find the display folder listed under the table node:

Analysis Services Tabular 2016 display folder in Excel

Unlike SSAS Multidimensional, there is no dropdown with existing display folders; you have to type everything manually (or copy and paste of course).

Assigning Multiple Display Folders

It is possible to assign one single measure or column to multiple display folders. You can do this by concatenating them using the semicolon. Let’s put the Tax Rate column in two display folders:

Multiple display folders in Analysis Services Tabular 2016

This gives the following result in Excel:

Analysis Services Tabular 2016 multiple display folders in Excel

It might be a bit confusing though if the same measures pops up multiple times in the field list.

Nesting Display Folders

A bit more interesting scenario is the ability to nest display folders. This is done by concatenating the display folders with a backslash. Instead of putting the Tax Rate in two distinct display folders, let’s nest them instead:

Nesting display folders in Analysis Services Tabular 2016

Which gives:

Nested display folders in Excel

Behavior in Different Client Tools

Excel

Excel supports display folders as you have noticed previously. However, it will display them in different places depending if the items in the display folder are measures or columns. For example, if some measures are added to a display folder called “Derived Measures”, we get the following:

Measures & display folders in Excel from Analysis Services Tabular 2016

SSAS Tabular

The display folder is moved to the “Order measure group”, although there’s no such concept in SSAS Tabular. If a display folder contains mixed objects – measures and columns- it will be repeated twice in the field list. The same is true for the browser in SQL Server Management Studio (since that one is still complete based on the Multidimensional layout):

Display folders in SSMS from Analysis Services Tabular 2016

Power BI Desktop

This screenshot was taken with version 17.2 of SSMS. Power BI Desktop however gladly mixes measures and columns together in its display folders:

Display folders in Power BI desktop in Analysis Services Tabular 2016

Reporting Services Query Designer

The Reporting Services Query Designer behaves the same way as Excel and SSMS:

Display folders in SSRS from Analysis Services Tabular 2016

Notice that while the query editor can produce DAX queries (available in the latest version of SSDT), the field list still displays all objects as if we’re dealing with a multidimensional model.

Conclusion

Display folders are nice addition to Analysis Services Tabular 2016 models. They allow you to easily create more structure in your models. It is possible to put objects in multiple display folders or to nest display folders. Depending on the client tool, display folders behave a bit differently for measures.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Tuesday, April 7, 2020 - 3:56:53 AM - Koen Verbeeck Back To Top (85298)

Hi Gabriel,

In Tabular, there's no such thing as a measure group. You only have tables. Tables can contain measures and as discussed in this tip, you can group measures together in a display folder in the same tabel. If you want to move a measure to another table, you can modify its properties. But of course the other table has to exist first. A work around is to create a table with one column and one single value, which is then hidden. Then you can use that table as some sort of measure group.

Regards,
Koen


Monday, April 6, 2020 - 4:33:20 PM - Gabriel Back To Top (85297)

Is it possible to move a measure into a new measure group?

Say I have one table (MyTable) with columns (A) and one measure (MyMeasure) within it. Can I move MyMeasure into a group (MyMeasureTable)?

It seems like I would have to create a new table to do so? It'd have to be an Empty table but Tabular doesn't allow that. 


Wednesday, August 14, 2019 - 1:45:57 AM - Koen Verbeeck Back To Top (82052)

Hi Derek,

in Power BI Desktop, you have to go to the modelling view. There you can set the display folders.

https://powerbi.microsoft.com/en-us/blog/modeling-view-in-power-bi-desktop/

In Tabular, you have to view the properties window of a measure or column.


Tuesday, August 6, 2019 - 12:16:31 PM - Derek Trujillo Back To Top (81980)

I new to the Microsoft development world and struggling to get all the pieces to fit.  I'm trying to see where I can add measures folders within either Power BI or SSAS Tabular and this solution looks pretty straightforward, but I don't have the option for Display Folder when looking at the tabular model in Visual Studio.  I'm using Visual Studio 2019 and SQL Server Analysis Services 15.0.1331.137. 
Once in Power BI - I don't have the option to add a Display Folder there either. 















get free sql tips
agree to terms