MDX calculated measure by dimension value

0
votes

I have to filter the measure value by a value in a the article group dimension.

Starting point:

Facts:

  • Inventory (only opening stock of a month)
  • Sales

Dimensions:

  • Articlegroup (content: articlegroup types: Service or not)
  • Article
  • Time

We have 2 types of sale:

  • Standard sale (affects invetory)
  • Service (doesn't affects inventory)

My MDX-Script calculates dynamically the daily stock over a month. The problem is that service-sales should not affects the inventory, so my script have to filter the measure value.

Such like:

CREATE MEMBER CURRENTCUBE.[Measures].[FilteredAmount]
AS (SELECT [Measures].[SalesAmount] FROM [Cube]
    WHERE [Article Group].[Service].&[0])
-- Service "0" = not service

CREATE MEMBER CURRENTCUBE.[Measures].[Amount]
AS [Measures].[OStockAmount] - [Measures].[FilteredAmount]

I don't know how to filter the value

1
ssasmdxdimensionscalculated-fieldfact

1 Answers

1
votes

Try this:

CREATE MEMBER CURRENTCUBE.[Measures].[FilteredAmount]
AS ([Measures].[SalesAmount], [Article Group].[Service].&[0]);
-- Service "0" = not service

That creates a tuple that just retrieves a section of the cube... Measure SalesAmount and Service=0