Create Calculated Measure based on dimension filter condtion

Question

I am trying to create a new calculated measure in my cube based on a dimension attribute value. Fact and Dimension table looks like below.

So, far below MDX script is generated from the form view.

 SELECT NON EMPTY { [Measures].[FactCount] } ON COLUMNS
,NON EMPTY { ([DimDate].[Year].[Year].ALLMEMBERS * [DimDate].[Month Name].[Month Name].ALLMEMBERS 
*
({FILTER(
[DimFilter].[Desc].[Desc], 
[DimFilter].[Desc].CurrentMember.Name = 'F1')
})


) } ON ROWS 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM [FactTest]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

However above script only returns for the dim filter value 'F1' How can I get the other measure columns(output table from image) from the cube.

Any kind of help suggestion is appreciated and thanks in advance for your help!

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-12-15T14:49:41

You meant the following query?

SELECT NON EMPTY { [Measures].[FactCount] } ON COLUMNS
    ,NON EMPTY { ([DimDate].[Year].[Year].ALLMEMBERS * [DimDate].[Month Name].[Month Name].ALLMEMBERS 
    *
    {[DimFilter].[Desc].[F1],[DimFilter].[Desc].[F2]}        

    ) } ON ROWS 
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
    FROM [FactTest]
    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Create Calculated Measure based on dimension filter condtion

1 Answers