Applicable to Tabular Compatibility Level:
SQL 2012 SP1 / SQL 2014 (1103) | SQL 2016 (1200) | SQL 2017 (1400)
Note: this feature is NOT supported in the 1200 compatibility mode for SSAS Tabular 2016 as Microsoft removed the undocumented hooks that we were using to expose this functionality. However, SSAS Tabular 2017 supports this feature natively.
SQL Server Data Tools 2012 (formerly BI Development Studio) Tabular projects allow adding hierarchies to tables. However, a UI for setting the HideMemberIf property is not provided. This prevents Tabular projects from creating ragged hierarchies. For example, in the Adventure Works Employee hierarchy, Stephen Jiang not only is a manager, but has also sold some products. Without setting HideMemberIf, a blank shows up in the hierarchy (left PivotTable). After setting HideMemberIf on Level3 to NoName, the blank employee name row drops out as desired (right PivotTable):
Warning: While the HideMemberIf setting works in Tabular models, it is not officially supported by Microsoft. If you encounter a bug in how Tabular handles HideMemberIf and open a support case, Microsoft may not provide support. For example, there are reports that Tabular HideMemberIf will cause Visual Studio to hang. There are other reports that HideMemberIf doesn’t work in PerformancePoint with Tabular models. We recommend you upgrade to 1200 compatibility level and use the officially supported feature.
In order to set HideMemberIf, switch to the diagram view where you manage hierarchies. Highlight one or more levels then right click and choose Set HideMemberIf…
When the dialog pops up set HideMemberIf. When you click OK the change is applied to the workspace database immediately. ProcessFull is required during this operation due to a bug in SSAS 2012.
Note that some changes like renaming levels will wipe out the HideMemberIf setting. BI Developer Extensions backs up the HideMemberIf setting in an annotation on the database. That way, the Tabular Pre-Build feature can prompt you to fix this setting.
After changing a HideMemberIf setting, BI Developer Extensions may prompt you for the credentials to a data source which is using stored credentials if you have not already entered those credentials during this SSDT editing session. This prompt ensures that Tabular HideMemberIf succeeds (since it needs to ProcessFull the table) and also that data source credentials in the workspace database do not get wiped out as HideMemberIf settings are applied.