locked
MDX Exists with Filter RRS feed

  • Question

  • Hi 

    I am trying to use Exists function to get some output but having trouble resolving the same. Following is my MDX where [Measures].[row_num] is used to get the SQL Row_number  (By Partition) type of function which is giving me values 1, 2,3,4.....and so on. In set [breakdown], I am filtering rows and only keeping rows with value [row_num] =1. Now for showing results, I want to show only one dimension attribute from [breakdown] set (i.e. I want to show only values for attribute [DimMeasuresReportingData].[MeasureId].[MeasureId]) so I have used Exists function to get those rows. I am expecting the sum of [Measures].[Met] values  of only filtered rows (i.e. rows with [Measures].[row_num] =1 values) but MDX is returning SUM of all rows instead i.e. result getting is SUM of [row_num] with values 1,2,3....But I want only SUM of [Measures].[Met] for those rows having [row_num]=1 value. Aggregate function for [Measures].[Met] is SUM and it's a measure created based on fact table column.

    with
    set [breakdown] as 
    Filter(
    	 NonEmpty({   
    		[DimMeasuresReportingData].[SurrogatePatID].[SurrogatePatID]   *
    		[DimMeasuresReportingData].[MeasureId].[MeasureId] * 
    		[DimMeasuresReportingData].[NetworkAggrPartition].[NetworkAggrPartition] *      
    		[DimMeasuresReportingData].[TINAggregate].[TINAggregate]
    			})
    	  ,[measures].[row_num] =1
    	  )
    
    Member [measures].[row_num] as 
    Rank(
    (   [DimMeasuresReportingData].[SurrogatePatID],[DimMeasuresReportingData].[MeasureId],
    	[DimMeasuresReportingData].[NetworkAggrPartition],
        [DimMeasuresReportingData].[TINAggregate]),
    				([DimMeasuresReportingData].[SurrogatePatID],[DimMeasuresReportingData].[MeasureId],
    	[DimMeasuresReportingData].[NetworkAggrPartition],
        [DimMeasuresReportingData].[TINAggregate].[TINAggregate]))
    
    
    Select 
    {[Measures].[Met],[measures].[row_num] } ON 0,  
    Exists( 
    		 [DimMeasuresReportingData].[MeasureId].[MeasureId],
    		 [breakdown]
    	  )
    on 1
    FROM 	
    [RMD]
    WHERE  ({[Date].[Date].&[20170101] : [Date].[Date].&[20171231] }, [Customer].[Customer].&[69])
    


    Aniruddha http://aniruddhathengadi.blogspot.com/




    Saturday, March 3, 2018 2:35 PM

Answers

  • Consider you final select statement. You place two members of the measures dimension on columns, a subset of the MeasureID attribute on rows, and with the filter axis you limit dates and customer. Once this has been done, the engine proceeds to calculate the values of the cells. By this time, the [breakdown] set has been used and discarded: it influenced the list of MeasureIDs on rows, and now it is not affecting cell values. To calculate [Measures].[Met] for only the members of the [breakdown] set, you could do e.g. SUM(existing [breakdown], [Measures].[Met]).

    As a side note, you seem to treat multidimensional cube space and MDX as though it were a variant of RDBMS tables and SQL. This is evident by the way you refer to 'rows' in your description. The two concepts are actually quite different, and the respective approaches are sometimes known as MDX mindset and SQL mindset.


    Expect me to help you solve your problems, not to solve your problems for you.

    Saturday, March 3, 2018 9:03 PM

All replies

  • Consider you final select statement. You place two members of the measures dimension on columns, a subset of the MeasureID attribute on rows, and with the filter axis you limit dates and customer. Once this has been done, the engine proceeds to calculate the values of the cells. By this time, the [breakdown] set has been used and discarded: it influenced the list of MeasureIDs on rows, and now it is not affecting cell values. To calculate [Measures].[Met] for only the members of the [breakdown] set, you could do e.g. SUM(existing [breakdown], [Measures].[Met]).

    As a side note, you seem to treat multidimensional cube space and MDX as though it were a variant of RDBMS tables and SQL. This is evident by the way you refer to 'rows' in your description. The two concepts are actually quite different, and the respective approaches are sometimes known as MDX mindset and SQL mindset.


    Expect me to help you solve your problems, not to solve your problems for you.

    Saturday, March 3, 2018 9:03 PM
  • Thanks Alexei !! Appreciate your help !! 

    Aniruddha http://aniruddhathengadi.blogspot.com/

    Sunday, March 4, 2018 8:21 AM