Where Clause using another Dimension Value

Question

I'm trying to create a Calculated Member in my cube with where clause but couldn't figure how to achieve the proper result.

I created a calculated member "Outlook" using the below code to display only Forecast values.

CREATE MEMBER CURRENTCUBE.[Measures].[Outlook]
 AS SUM(([Source Profile].[Source Profile Hierarchy].CurrentMember,
         [Source Profile].[Profile Level01].&[Outlook]),
        [Measures].[USD Amount]), 
FORMAT_STRING = "#,##0;(#,##0)", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'USD' ,  ASSOCIATED_MEASURE_GROUP = 'CARS';

Cube Result

Now I would like to filter the results dynamically based on another hidden dimension "Current_Month". This dimension always has current financial period value and it's corresponding outlook profile

Year_Month  Outlook_Profile
2015010     10 + 2

Expected result should be "Outlook" measure showing value based on Current_Month dimension, which is '10 + 2' and rest of them should be 0

Expected result

Just to explain the requirement in SQL terms, I would like to achieve the below in MDX

Where Fact.Source_Profile=Dimension.Source_Profile

instead of

Where Fact.Source_Profile='10 + 2'

I'm not sure how to achieve this in Where Clause or by another means. I could see examples of hard coding values, like year.&[2015] but haven't seen one using dynamic values.

I'm not sure if I posted my question clearly :( All I would like to do is (in SQL terms) Where Fact.Source Profile = Dimension.Source Profile. The Dimension.Source Profile changes every month and I'm not sure how to achieve this. — San83

San83 San83 · Accepted Answer · 2015-12-04T12:50:04

I found a solution myself and thought of sharing the same. Used StrToMember function to pass hidden dimension's member as a variable here.

CREATE MEMBER CURRENTCUBE.[Measures].[Outlook]
 AS (
        Sum
            (
                (
                    [Source Profile].[Source Profile Hierarchy].CurrentMember,
                    strtomember("[Source Profile].[Source Name].&[" + [Outlook Profile].[Outlook Profile].&[1].Member_Caption + "]")
                )
            ,[Measures].[USD Amount]
        )
), 
FORMAT_STRING = "#,##0;(#,##0)", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'USD' ,  ASSOCIATED_MEASURE_GROUP = 'CARS'  ;

Where Clause using another Dimension Value

1 Answers