SSAS MDX query

All replies

• 

  

  0

  Sign in to vote

  Hi Vidya,

  According to your description, you want to show the customers which have contracts/no contracts. Right?

  For getting the customers which have at least one contract, we can use Nonempty() to achieve it. Please refer to the sample query below:

  SELECT   
   [Measures].[Contract] ON COLUMNS,
   { 
      NonEmpty 
      ( 
        [Customer].[Customer].Children 
       ,[Measures].[Contract] 
      ) 
    } ON ROWS 
  FROM [Cube];
  

  For getting the customers which have no contract, we can use IsEmpty() to return the empty cells. Please refer to the sample query below:

  with member [Measures].[x] as
  isempty([Measures].[Contract])
  select [Measures].[x] on 0,
  filter([Customer].[Customer].[Month],[Measures].[x]=true) on 1
  from [Cube]

  Reference:
  MDX : Non Empty v/s NonEmpty
  IsEmpty (MDX)
  Filter (MDX)

  If you have any question, please feel free to ask.
  

  ---

  Simon Hou
  TechNet Community Support

  
  

  
  

  • Edited by Simon_HouMicrosoft contingent staff Thursday, February 19, 2015 8:56 AM

  Wednesday, February 18, 2015 7:50 AM
• 

  

  0

  Sign in to vote

  Hi Simon,

  Thanks for the reply.

  I am very new to MDX query.

  I have designed cube with star schema like

  1)Sales Analysis Fact table contains -

  Customer_dkey, Sales_Amount,Flag_is_under_contract,Flag_is_under_CAA_contract,

  Flag_is_under_CAP_contract,Flag_is_out_of_contract

  2)Dim_Customer-directly link to the fact with Customer_dkey having Customer information only.
  

  3)Dim_ CCM_CustomerContract_Association having - Customer_dkey, Contract_dkey

  4)Dim_Contract having contract information-Contract_number,Name_type_contract

  So I have created one degenerated dimension Dim_sales from Sales Fact and flag containing values 0 and 1.

  Dim_Sales containing

  Flag_is_under_contract

  Flag_is_under_CAA_contract

  Flag_is_under_CAP_contract

  Flag_is_out_of_contract

  So I want Calculated member having Flag_is_under_contract<>0 and vice versa.

  And my requirement is like

  1)Customer under annual contract from the flag Flag_is_under_CAA_contract>0

  2)Customer under promotional contract from the flag Flag_is_under_CAP_contract>0

  3)Customer under contract from the flag Flag_is_under_contract

  4)Customer out of contract from the flag Flag_is_out_of_contract

  Summary:In my fact table i have defined the flags having value 0 and 1.I have created one dimension from fact table having these four flags.Now i want the customer having at least one contract based on the flag.I want to achieve this is in calculated member.

  Thanks in Advance.If you want more clarification then let me know please.

  Best Regards,

  Vidya.

    

  

  
  

  Sunday, February 22, 2015 9:42 AM
• 

  

  0

  Sign in to vote

  Hi,

  It will help full to send the dimension and fact table structure.

  Thanks,

  Monday, August 24, 2020 12:01 PM