Hiding Dim Values Not Having Corresponding Measure Values in BI Answeres

In one of our applications, there is a dimension with the following hierarchy

Tier 1
Tier 2
Tier 3 etc

Using the above dim and a measure created the following report.


When a pivot table view was added to the report with Tier 1 in the Pages, Tier 2 as Column and Tier 3 as the Rows, following is the view generated



In this view, all the Tier 2 values are dispalyed even though the combination of Tier 1, Tier 2 and Tier 3 does not have any data.

What is expected from the view is:



The Tier 2 values should not be displayed if they don’t have any data for Tier 1 and Tier 3.

To hide the Tier 2 columns, click on the Content Properies icon available on top of the Rows.



This will display the Content Properties window. Check the “Limit columns based on section values” check box.



Click on Ok.

When the view is refreshed, it will not show the Tier 2 values not having any data in combination with Tier 1 and Tier 3.