locked
SSAS 2012 Multidimensional filtering RRS feed

  • Question

  • I have a fact table which holds properties (houses, buildings etc) and another table (not sure how to set this up that's the question) that holds many per property their first mortgage amount, date, ...

    second mortgage amound date ,...

    and so on (up to 4 of these)

    They want to be able to sum all morgage amounts per property or find out if any mortgage amount is between 25K and 50K for a property show the property, how many mortages per property are between 25K and 50K

    Which properties have any mortgages within year 2012 if yes how many etc

    How can I set this up so that this questions can be answered, would that be another dimension (only 1 table) or a shared dimension and another fact table, how would that be related (at sql server table level, foreign keys etc)?

    Thank you


    Gokhan Varol

    Tuesday, July 30, 2013 6:09 PM

Answers

  • Hi Gokhan,

    We can write MDX code to get the expected result, please refer to the following MDX query:
    WITH MEMBER [Measures].[Count] AS Count(FILTER([Date].[Date].[Date].MEMBERS,
         [Measures].[Internet Sales Amount]>10000 and [Measures].[Internet Sales Amount]<20000)
      )

    SELECT [Measures].[Count] ON 0,
     
    [Product].[Category].[Category].Members  ON 1

    FROM  [Adventure Works]

    For more information about Count() and Filter() functions, please see:
    http://technet.microsoft.com/en-us/library/ms144823.aspx
    http://technet.microsoft.com/en-us/library/ms146037.aspx

     

    Best Regards,


    Elvis Long
    TechNet Community Support

    • Marked as answer by Elvis Long Tuesday, August 6, 2013 10:22 AM
    Thursday, August 1, 2013 9:50 AM