MDX in Dashboards, Scorecards, and Views?

You can use MDX in Dashboard Designer in three areas: filters, KPI data mappings, and the Analytic View Designer. This capability allows to you do advanced selections and queries for OLAP data sources that may not be possible through the designer user interface.


By using MDX in filters, you can provide your dashboard users with more relevant views based on their filter selections. For example, you can write MDX for the Region filter to display the top 10 products based on Unit Sales for the selected region. Or you can write MDX for a multi-select Region filter to display the five cities with the greatest Sales Amount for each region that is selected.

The following chart uses a single filter to display both of the following views:



The filter uses MDX to display the children of the selected member to the view: France, Germany, and Italy for Europe and Canada, Mexico, and USA for North Amer.

The simple MDX used to create the filter is shown below:


In the example above, <<UniqueName>> represents the MDX syntax for the selected member (such as [Geography].[Geography].[Region].&[1] for Europe). The appended Children operation is an MDX function that returns all items immediately below the selected item in the OLAP hierarchy.

Using a more advanced example, the following view returns the top two cities for each country selected:


With the multi-select parameter, users can select one or more countries to compare. The same view and filter above creates the following grid, simply selecting different countries:


The MDX used to create this filter is shown below:

Hierarchize(Union(<<UniqueName>>, Generate(<<UniqueName>>,

TopCount(Descendants([Geography].[Geography].CurrentMember, [Geography].[Geography].[City]), 2, ([Measures].[Sales Amt], [Time].[FY Year].&[2006]) )) ))

KPI Data Mappings

Typically, you will use MDX in KPI Data Mappings for Target values, where the target value is a calculation based on another member. For example, you want the Target value of a Sales KPI to be 10% greater than the same period last year (year-over-year growth). You may also need to use an MDX expression to select a calculated member because this capability is not available in the selection tree.

Using the MDX function "PrevMember," this MDX returns the Sales Amount value for the prior time period, multiplied by 10%.

([Measures].[Sales Amt], [Time].[Fiscal].CurrentMember.PrevMember)*1.1

Here's how it appears in the scorecard:


Using the MDX function "ParallelPeriod," this MDX returns the Sales Amount value for the same time period the prior year, multiplied by 20%.

([Measures].[Sales Amt], ParallelPeriod([Time].[Fiscal].[Year],1, [Time].[Calendar].CurrentMember))*1.2

Analytic View Designer

The Analytic View Designer, displayed when you create an Analytic Chart or Analytic Grid, provides an MDX mode, located on the Query tab, where users can provide advanced MDX for their views.


This mode is useful when you want to create an analytic view that cannot be created using the drag-and-drop user interface on the Design tab, such as a view that uses a top count filter. Users can provide any valid MDX in this editor.

Note Views created on the Query tab using MDX do not support ad hoc navigation. Users will not be able to drill down or expand on these views.


Elaine Andersen (


Comments (6)

  1. Bobby_Kotti says:


    I have two date list filters one is for Fromdate and another is for Todate. My KPI should get the actual values from the cube for given date range(Between Fromdate and Todate).Can u show me how to create MDX tuple formula for this.

    Thanks in advance.

    My mail id is


  2. vpfaiz says:

    Hi Bobby,

    I had a discussion with Alyson Powell Erwin of PerformancePoint Team in Microsoft and she confirmed that this is not possible. She took it as a suggestion and assured that she will consider it for the next release.

  3. skanigicherla says:

    HI vpfaiz

    I am doing one pilot project in which i had the requirement like having drill down option in the consolidate perfomance point server report by using mdx query is there any possibility?I am having some hard time regarding this problem please help me out.

    thanks and regards


  4. Ahmad Elayyan says:


    i Create a Filter which contain the Parent values.

    then i create Scorecard which contain the Children.

    then i create a Link Filter into Scorecard with the following Formula:


    so can i get the Children for the Parent Dim.

    Please suppose that i create a KPI with Actual and Target Just without any (Select Dimension) into KPI.

    then I add the values into Scorecard from the Hierarchy.


    Ahmad Elayyan

  5. Pooja says:


    We are trying to change weekly prior year calculation for year 2015 based on the attached time calendar.   Business requirement is for current year week1 Prior year calculation to return week2 data. Modified mdx is my first take at changes to prior year period calculations.

    Original MDX Statement: Prior Year

    SCOPE [Time Calculations].[Time Calculation Name].[Prior Year];  

       SCOPE [Date].[Period Hierarchy].[Date].Members;   –We need to keep the data. The 2008/2009 weeks off.                                

           THIS = ([Time Calculations].[Time Calculation Name].[Current],

                         ParallelPeriod([Date].[Period Hierarchy].[Year Number], 1, [Date].[Period Hierarchy].currentmember));                

       END SCOPE;                  

    END SCOPE;  

    Modified MDX Statement: Prior Year  

    SCOPE [Time Calculations Week Hierarchy].[Time Calculation Week Hierarchy Name].[Prior Year];

     CASE [Date].[Week Hierarchy].[Year Number] WHEN 2015


     SCOPE [Date].[Week Hierarchy].[Date].Members;                                

           THIS = ([Time Calculations Week Hierarchy].[Time Calculation Week Hierarchy Name].[Current],

                         ParallelPeriod([Date].[Week Hierarchy].[Year Number], 1, [Date].[Week Hierarchy].currentmember)+1);                

       END SCOPE;                  


       SCOPE [Date].[Week Hierarchy].[Date].Members;                                

           THIS = ([Time Calculations Week Hierarchy].[Time Calculation Week Hierarchy Name].[Current],

                         ParallelPeriod([Date].[Week Hierarchy].[Year Number], 1, [Date].[Week Hierarchy].currentmember));                

       END SCOPE;                  



    Can you take a look and propose how it can work?


    Pooja Sund

Skip to main content