Dashboard filters is one of the key components of BI Platform that drive reports and KPI’s in various dashboards. MDX expressions to a great extent enables to customize PerformancePoint filters and enhance the end user experience.
First – a quick look at member selection option. Member selection is the easiest way to build dashboard filters which does not require MDX expressions (Screen capture 1). Member selection option allows to select specific members, although this is not usually recommended as the filter can become quite static. A better method is to select Children, Leaves or Descendants with additional option to include or exclude default member. For hierarchies, member selection option also allows selection members from a particular level of hierarchy.
Screen Capture 1 – Member Selection
While member selection option may be sufficient for some business scenarios, using MDX Expressions (Screen Capture 2) enables to customize dashboards without hardcoding the member selection.
Screen Capture 2 – MDX Expression Option for PerformancePoint Filters
Some common and often repeated dashboarding scenarios where MDX expressions for PerformancePoint filters are useful is discussed below
Display only members that have data
It’s not particularly intuitive for business users to select an item from drop down only to find that there is no data for the dashboard to display.
The following example uses FILTER function to select only Product Categories that have a sale.
FILTER([Product].[Category].Children,[Measures].[Internet Order Count] > 0)
Screen Capture 3 – MDX Expression for PerformancePoint Filter
Display only last N time periods
Often business is only keen on trend analysis for the last few years. All time periods from beginning of time need not be displayed in dashboard.
The following MDX expression displays the last 4 fiscal years from current fiscal year. When the fiscal year changes so does the filter data. Note that for this MDX expression to work, the date dimension must have current attributes defined.
(EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0).LAG(-4) : EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0))
Display time periods in reverse chronology – latest dates first
Another common requirement for business users is to see the latest dates first.
Using the ORDER function in the previous MDX expression, the latest fiscal years can be displayed as the first item in the drop down.
ORDER((EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0).LAG(-4)
: EXISTS([Date].[Fiscal Year].Children,[Date].[Current Fiscal Year].&[Y]).Item(0))
, [Date].[Fiscal Year].CurrentMember.Properties('key'),DESC)
Hide unknown members
It a good to handle unknown dimension members in datamarts which in turn helps to address data quality issues. However bad data need not be displayed in dashboard filters.
The following MDX expression gets all the Sales Reason members except the unknown members.
([Sales Reason].[Sales Reason].Children -[Sales Reason].[Sales Reason].&[UNAVAILABLE] -[Sales Reason].[Sales Reason].&[NOT APPLICABLE] -[Sales Reason].[Sales Reason].&[UNKNOWN])
The above examples demonstrate the ease with which seriously rocking PerformancePoint filters can be created using simple MDX expressions that create business friendly dashboards.
Benny Austin
RT @BennyAustin MDX Expressions for PerformancePoint Filters goo.gl/d9XIY #performancepoint #mdx
—
João Lopes (@SQLSniper) January 15, 2013RT @BennyAustin [NEW POST] MDX Expressions for PerformancePoint Filters wp.me/pxNuz-ts #PerformancePoint #MDX #SharePoint #msbi
—
Derek Goodridge (@workerthread) January 14, 2013
Benny Austin 
Hi Benny,
Great Post.Thanks to you i could apply the above logic for my financial years.But I also have a filter for months whose values are strings,so they are displayed ascending or descending alphabetically and not according to the lastest month.
Is there a way i could sort them according to the month number but display the string in the mdx filter query
Namrata, it is better to address this in cube instead of using MDX expression in PPS filter. In your date dimension, set the attribute key as month number and attribute name as month desc and sort by attribute key instead of attribute name.
I have a grid with 7 different columns.
I want to filter one of the columns so it only displays information from the last day of the month.
Is there a way to do this? I know there is a <>
but what I want is something like
pseudo code:
CASE
WHEN <> = [Measures].[MeasureX]
THEN trail(date dimension, 1) //give last date of current month
ELSE 0
END
Craig, you should create a calculated member (in MDX query) or a calculated measure (in cube) instead of a filter. Your MDX will be using the original measure and will be something like this
WITH MEMBER [Measures].[Y]
AS ([Date].[Calendar].CurrentMember.Parent.LastChild,[Measures].[X] )
SELECT { [Measures].[X],[Measures].[Y]} ON COLUMNS,
[Date].[Calendar].[Month].&[201403].Children ON ROWS
FROM [Adventure Works]
Nice post. Thanks. But I would like to specify default values also via MDX. Is it possible? If yes, kindly guide us.
Thanks,
Merin
Merin, PerformancePoint filters handles default values a little bit different because of personalization feature in SharePoint portal. PerformancePoint filters caches last used filter values per user for 90 days by default. This is a setting in PerformancePoint service application that is configurable from SharePoint central administration. As such there is no capability to determine default values using MDX query.