What's the best way to filter MDX on a measure value?

• Dan Colbert-387424

  SSCrazy

  Points: 2195

  More actions

  November 24, 2008 at 7:59 am

  #383446

  I have a FactOrders cube.

  I have a user that wants to return all orders that have a $0 value in the product price paid. Is there a way to filter on a measure? Do I need to create a separate dimension in SSAS 2005 related to order value?

  I'm pretty green when it comes to Analysis Services, so any help would be appreciated.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  November 24, 2008 at 9:43 am

  #902583

  There is actually a ValueFilter() MDX function just for this.

  It is a relatively slow function, so avoiding it is typically a good idea.

• Kathleen Bayles

  Valued Member

  Points: 61

  More actions

  November 25, 2008 at 12:26 pm

  #903369

  You can use the HAVING fx in MDX here is an example query with the syntax:

  SELECT {[Measures].[OrderDollars]}ON AXIS(0),

  {[Location].[Location Name].children}

  HAVING [Measures].[OrderDollars]=0 ON AXIS(1)

  FROM [Orders]

  WHERE ([Date].[Calendar].[9-Jun-08])