SSAS Cube in Excel - Show only applicable rows with KPI? I have a cube built in SSAS 2008r2.  I'm exposing it for the moment through an excel PivotTable.

I recently added a KPI to the cube, and when I show the KPI in my PivotTable interface it causes non-applicable dimension values to be shown at the current level.

For instance, suppose I have customers:

CustomerA
CustomerB
CustomerC


And States:

NY
VA
TX


When I look at the PivotTable without the KPI, CustomerB has no rows under NY because they have no locations there.

When I add my KPI in and display just the value of the KPI, all is well.

When I add in the status indicator (i.e. traffic light or whatever),  I see every customer under every state.

Is this expected behavior? Is there a way around it?  I like the KPI but it defeats the purpose if I get every value of my dimension at each level.

Some more details:

The Value Expression for the KPI is just a calculated measure, which works fine.

The Status Expression is:

CASE
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") &gt; .90
        THEN 1
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") &lt;= .90
        AND KpiValue("MyKPI") / KpiGoal("MyKPI") &gt; .75
    THEN 0
ELSE -1
END
Thanks to @ConcernedOfTunbridgeWells for pointing me in the right direction on this.

The issue was with the status expression.

Since it ALWAYS returns a 1, 0 or -1 it didn't account for NULLs.

I added a check at the beginning of the expression:

CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL

...which correctly leaves  those fields empty.