CODEWITHSUNDEEP
mdxolap-cube
Eliza M
Eliza M

Reputation: 107

MDX - set member by another member values

I have this table generated with MDX.

http://i62.tinypic.com/2m5xeg0.jpg

I need to create a calculated dimension (or a measure) called "this year" with 2 values:
YES: if currentmember YEAR includes value 2015 and
NO: if member YEAR does not include 2015 for every CLIENT ID

http://i57.tinypic.com/2h508yx.jpg

The MDX code is: 

SELECT
    NON EMPTY {[Measures].[QUANTITY]} ON COLUMNS,
    NON EMPTY CrossJoin({CLIENT ID].[CLIENT ID].Members, {[YEAR].[YEAR].Members}) ON ROWS
FROM
    [MyCube]

Upvotes: 2

Views: 1318

Answers (2)

Eliza M
Eliza M

Reputation: 107

Thank you very much! The alternative answer worked perfectly in Mondrian with very little changes:

WITH 
  MEMBER [Measures].[is2015] AS 
    IIF
    (
        (
          [CLIENT ID].CurrentMember
         ,[YEAR].[2015]
        )
      > 0
     ,"YES"
     ,"NO"
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[QUANTITY]
     ,[Measures].[is2015]
    } ON COLUMNS
 ,NON EMPTY 
    CrossJoin
    (
      [CLIENT ID].[CLIENT ID].MEMBERS
     ,{[YEAR].[YEAR].MEMBERS}
    ) ON ROWS
FROM [MyCube];

Upvotes: 2

whytheq
whytheq

Reputation: 35557

You could try something like the following:

WITH 
  MEMBER [Measures].[is2015] AS 
    IIF
    (
      Isnull(Instr(0,[YEAR].[YEAR].CurrentMember.Member_Caption,"2015"))  //<<unsure if the 2nd argumnet should read: [YEAR].CurrentMember.Member_Caption
     ,"YES"
     ,"NO"
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[QUANTITY]
     ,[Measures].[is2015]         
    } ON COLUMNS
 ,NON EMPTY 
    CrossJoin
    (
      [CLIENT ID].[CLIENT ID].MEMBERS
     ,{[YEAR].[YEAR].MEMBERS}
    ) ON ROWS
FROM [MyCube];

Here is the MSDN documentation for the Instr function used within mdx: https://msdn.microsoft.com/en-us/library/hh758424.aspx

Referring to your screen-print please try this alternative:

WITH 
  MEMBER [Measures].[is2015] AS 
    IIF
    (
        NonEmpty
        (
          [CLIENT ID].[CLIENT ID].CurrentMember
         ,[YEAR].[YEAR].[2015]
        ).Count
      > 0
     ,"YES"
     ,"NO"
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[QUANTITY]
     ,[Measures].[is2015]
    } ON COLUMNS
 ,NON EMPTY 
    CrossJoin
    (
      [CLIENT ID].[CLIENT ID].MEMBERS
     ,{[YEAR].[YEAR].MEMBERS}
    ) ON ROWS
FROM [MyCube];

Upvotes: 2

Related Questions