Overview:  A SQL Server Analysis Services calculation to retrieve the description, on a single row, for a value which differs from period to period.  Useful when retrieving a flattened rowset for the purpose of generating a report from Reporting Services.

In the following example (shown using the cube browser in BIDS), we have values from 0 through 5.  When the time period is added to the columns, and a Metric Name label on the rows, our measures line up nicely from year-to-year when browsing a cube:

    

The above values are meaningless without some sort of description to help the user understand its meaning.  However, when we add the description associated to the value, we end up with 2 rows per Metric Name, which is not quite what we want:

    

Instead of the above, what I want is for the data to remain on one row.  This was very important so that I could structure a Reporting Services report correctly.  This is an example of what I do want:

    

I’m sure there’s several ways to do this … I accomplished it using the following calculated member:

Filter([Metric].[Scale Member Name].children,

       NOT IsEmpty([Measures].[School Measure Value])

       ).Item(0).name

    

To break down the expression:

• Filter:  Returns the set that results from filtering a set based on a search condition. 
• .Children:  This is passing a Children function to the Member being retrieved by the Item(0).name.  If I didn’t specify Children, I would get “All” returned for each row.
• NOT IsEmpty:  Returns the member name for a non-null measure only.
• .Item(0).name:  Since the Filter returns a set, we want to use the .Item to return the first tuple of the set only.  The .name specifies we want to see the member name.

Please keep in mind that this approach only works with a “strict” decode list – in my case, I have one and only one description that can equate to a value.  That data integrity is maintained by the ETL process in my situation, so in the above example I didn’t include a condition to gracefully handle the existence of bad data.

Finding More Information

Kevin Goff’s Blog:  When PREVMEMBER alone isn’t enough in MDX

MSDN:  Filter Function

MSDN:  Children Function

MSDN:  IsEmpty Function

MSDN:  Item (Tuple)