How to filter data based on two values using MDx

• Mkrish

  Ten Centuries

  Points: 1256

  More actions

  September 19, 2014 at 2:02 am

  #290092

  I have an ssrs report which i created by using Mdx query

  i have to filter amount based on two dimension values that is [travel cost] which comes from [Cost head] dimension and [rechargeble] which comes from [Rechargeble] dimension

  i tried by using below query but iam not getting

  set [RechargableData] as

  Filter([Cost Head].[Cost Head Name] .members,

  [Cost Head].[Cost Head Name].&[Travel Costs]

  )

  member [Measures].[TotalRecharagebleTravel] as Sum([Rechargeable].[Description].&[Rechargeable],[Measures].[Amount] )

  can any one please suggest me how to get this

• PB_BI

  SSCoach

  Points: 17463

  More actions

  September 19, 2014 at 2:23 am

  #1746611

  You need to create a set of the two members to get the intersect and then sum it. Here's an example on adventure works for bike sales in the UK:

  WITH MEMBER [UK Bikes]

  AS

  SUM(

  {([Product].[Category].&[1],[Geography].[Country].&[United Kingdom])}

  ,[Measures].[Reseller Sales Amount])

  SELECT

  {[UK Bikes]} ON 0

  FROM

  [Adventure Works]

  Hope that helps 🙂

  ---

  I'm on LinkedIn