MDX - set member by another member values

Question

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]

whytheq · Accepted Answer · 2015-08-10T13:16:54.037

2

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];

edited Aug 10 '15 at 13:16

answered Aug 10 '15 at 12:15

whytheq

34,466
65
172
267

Thank you for answer but is not quite the solution i need here. The [Measure].[is2015] should be for the whole "CLIENT ID" member, not for every single YEAR in row. If a certain client (40407 in this case) should have YES to all [YEAR] because it includes value "2015" (img. 2). I think a calculated dimension would be more appropriate, but I don't know how to set it. I forgot to mention I use MONDRIAN. – Eliza M Aug 10 '15 at 12:51
ok - this is still easy enough via the `NonEmpty` function. Do you have `NonEmpty` in Mondrian? – whytheq Aug 10 '15 at 13:13
WITH MEMBER [Measures].[is2015] AS IIF ( Isnull(Instr(0,[YEAR].[YEAR].CurrentMember.Properties("MEMBER_CAPTION"),"2015")) ,"YES" ,"NO" ) returns error: **MondrianException: Mondrian Error:No function matches signature 'Isnull()'** and the same error appears with the nonempty function. Mondrian has both function but it seems that something is wrong – Eliza M Aug 10 '15 at 13:19
**MondrianException: Mondrian Error:No function matches signature 'NonEmpty()'** – Eliza M Aug 10 '15 at 13:21
@ElizaM as I noted in first script, you might need to try this `[YEAR].CurrentMember.Member_Caption` instead of this `[YEAR].[YEAR].CurrentMember.Member_Caption`. Similarly in the second you might like to try this `[CLIENT ID].CurrentMember` instead of this `[CLIENT ID].[CLIENT ID].CurrentMember` – whytheq Aug 10 '15 at 13:26

score 2 · Answer 2 · answered Aug 10 '15 at 14:09

2

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];

answered Aug 10 '15 at 14:09

Eliza M

107
9

If your cube is big then it would be good to replace `"NO"` with `null`. `IIF` measures run more efficiently if one of the branches resolves to null as the calculation will be sparse and therefore quicker. – whytheq Aug 10 '15 at 20:30

MDX - set member by another member values

2 Answers2