SSAS MDX query RRS feed

  • Question

  • Hi Everyone,

    I have following query regarding mdx,

    My Dimension having structure and data like,

    I want the mdx query from above dataset

    1)Customer having at least one contract classified DPA(Contracts_BU) 

    2)Customer having no contract

    Dim_Contracts_dkey Contracts_BU Contracts_number Customer_number
    1 DPA 445125 121
    2 DPA 122
    3 DPA 445188 121
    4 DPA 445189 123
    5 DAC 445190 124
    6 DPA 125
    7 DAC 436394 123
    8 DPA 436395 128
    9 DAC 129
    10 DAC 436441 130
    Monday, February 16, 2015 11:06 AM

All replies

  • 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:


    [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
    select [Measures].[x] on 0,
    filter([Customer].[Customer].[Month],[Measures].[x]=true) on 1
    from [Cube]

    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

    Wednesday, February 18, 2015 7:50 AM
  • 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,


    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





    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,




    Sunday, February 22, 2015 9:42 AM
  • Hi,

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


    Monday, August 24, 2020 12:01 PM