MDX - filter measure by dimension

Question

I'm new to MDX and I'm currently trying to filter [Measures].[Sales Invoice Line Amount] by { [NAV CINNOST].[DIM_Code_Cinnost].&[AAA], [NAV CINNOST].[DIM_Code_Cinnost].&[BBB], [NAV CINNOST].[DIM_Code_Cinnost].&[CCC] }

I only need to filter THIS measure (Sales Invoice Line Amount). Not the others. Can you guide me how to rewrite the query?

     SELECT
      NON EMPTY [Customer].[Customer No].[Customer No] DIMENSION PROPERTIES
      member_name, parent_unique_name ON ROWS,
      {
        [Measures].[Value Entry Item Ledger Entry Quantity],
        [Measures].[Unit Margin],
        [Measures].[Sales Invoice Line Amount]
      } ON COLUMNS
    FROM [QTY Margin]
    WHERE (
            {
              [Date].[Calendar Month].&[201407]
            },
            {
              [NAV CINNOST].[DIM_Code_Cinnost].&[AAA],
              [NAV CINNOST].[DIM_Code_Cinnost].&[BBB],
              [NAV CINNOST].[DIM_Code_Cinnost].&[CCC]
            }
          ) CELL PROPERTIES value, formatted_value, fore_color, back_color

score 1 · Accepted Answer · answered Jul 17 '14 at 12:03

1

The quickest solution is using a calculated member that does the job you're looking for. Assuming you add over this dimension :

WITH
  MEMBER [My Sales Invoice Line Amount] AS Sum( {[NAV CINNOST].[DIM_Code_Cinnost].&[AAA],[NAV CINNOST].[DIM_Code_Cinnost].&[BBB], [NAV CINNOST].[DIM_Code_Cinnost].&[CCC]},
                                           [Measures].[Sales Invoice Line Amount]) 
SELECT
  NON EMPTY [Customer].[Customer No].[Customer No] ON ROWS,
  {[Measures].[Value Entry Item Ledger Entry Quantity],
    [Measures].[Unit Margin],
    [Measures].[My Sales Invoice Line Amount]
  } ON COLUMNS
FROM 
    [QTY Margin]
WHERE 
    [Date].[Calendar Month].&[201407]

answered Jul 17 '14 at 12:03

ic3

7,917
14
67
115

Thank you very much. It works. One more question. Is it possible to add more expression sets to Sum function? For example filtering MEMBER [My Sales Invoice Line Amount] by [DIM_Code_Cinnost] AND [Calendar Month]? – Rutz Jul 17 '14 at 12:34
yes you can add a set of tuple, e.g. {[NAV CINNOST].[DIM_Code_Cinnost] } * { [Date].[Calendar Month].&[201407] } in the sum function. – ic3 Jul 17 '14 at 13:38

MDX - filter measure by dimension

1 Answers1