ssas - MDX Calculated member with filter

Ssas - MDX Calculated member with filter

To create a calculated member with a filter in MDX (Multidimensional Expressions) within SSAS (SQL Server Analysis Services), you can use the FILTER function along with the MEASURES or [Measures].[...] reference. Here's a general syntax to achieve this:

WITH MEMBER [Measures].[CalculatedMemberName] AS ( -- Your expression here SUM( FILTER( [Dimension].[Hierarchy].[Level].Members, -- Your filter condition here [Dimension].[Hierarchy].CurrentMember.Properties("Property_Name") = 'Value' ), [Measures].[Your_Measure] ) ) SELECT { [Measures].[CalculatedMemberName] } ON 0, { [Dimension].[Hierarchy].[Level].Members } ON 1 FROM [YourCube];

Here's an explanation of the components:

  • WITH clause: Defines the calculated member.
  • MEMBER [Measures].[CalculatedMemberName]: Specifies the name of the calculated member.
  • SUM(...): Aggregation function to sum up the measure values based on the filtered set.
  • FILTER(...): Filters the members of a set based on a condition.
  • [Dimension].[Hierarchy].[Level].Members: Specifies the level or hierarchy from which you want to filter the members.
  • [Dimension].[Hierarchy].CurrentMember.Properties("Property_Name") = 'Value': Defines the condition for the filter. You can use any property or attribute of the dimension member for filtering.
  • [Measures].[Your_Measure]: Specifies the measure you want to aggregate.

Replace [Dimension].[Hierarchy].[Level], Property_Name, and 'Value' with the appropriate dimension, hierarchy, property name, and value based on your cube structure and requirements.

For example, let's say you want to create a calculated member that sums up the measure [Measures].[Sales] only for products with a specific category, you can use the following MDX:

WITH MEMBER [Measures].[TotalSalesForCategoryX] AS ( SUM( FILTER( [Product].[Product].[Product].Members, [Product].[Category].CurrentMember.Properties("CategoryName") = 'Category X' ), [Measures].[Sales] ) ) SELECT { [Measures].[TotalSalesForCategoryX] } ON 0, { [Product].[Product].[Product].Members } ON 1 FROM [YourCube];

This will create a calculated member [Measures].[TotalSalesForCategoryX] that sums up the measure [Measures].[Sales] only for products belonging to the category 'Category X'. Adjust the dimension and hierarchy names as per your cube structure.

Examples

  1. SSAS MDX calculated member with filter example

    • Description: Create a calculated member in SSAS (SQL Server Analysis Services) MDX query with a filter condition.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Product].[Product].MEMBERS, [Product].[Product].CurrentMember.Properties("Category") = "Electronics" ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  2. SSAS MDX calculated member with date filter

    • Description: Define a calculated member in SSAS MDX that applies a date filter condition.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Date].[Calendar].MEMBERS, [Date].[Calendar].CurrentMember.MemberValue <= NOW() AND [Date].[Calendar].CurrentMember.MemberValue >= NOW() - INTERVAL 1 YEAR ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  3. SSAS MDX calculated member with multiple filters

    • Description: Create a calculated member in SSAS MDX with multiple filter conditions applied.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Product].[Product].MEMBERS, [Product].[Product].CurrentMember.Properties("Category") = "Electronics" AND [Product].[Product].CurrentMember.Properties("Subcategory") = "Phones" ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  4. SSAS MDX calculated member with parameter filter

    • Description: Implement a calculated member in SSAS MDX that filters based on a parameter value.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Product].[Product].MEMBERS, [Product].[Product].CurrentMember.Properties("Color") = @ColorParameter ), [Measures].[Sales Amount] )' PARAMETER [ColorParameter] AS 'Blue' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  5. SSAS MDX calculated member with dynamic filter

    • Description: Create a dynamic calculated member in SSAS MDX that applies a filter based on a dynamic condition or parameter.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Product].[Product].MEMBERS, [Product].[Product].CurrentMember.Properties("Color") = [Product].[Product].[Color].CURRENTMEMBER.NAME ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Product].[Product].[Color].MEMBERS ON ROWS FROM [Adventure Works]

  6. SSAS MDX calculated member with time period filter

    • Description: Define a calculated member in SSAS MDX that filters data based on a specific time period.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Date].[Calendar].MEMBERS, [Date].[Calendar].CurrentMember.MemberValue <= STRTOMEMBER("2003-06-30") AND [Date].[Calendar].CurrentMember.MemberValue >= STRTOMEMBER("2003-01-01") ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  7. SSAS MDX calculated member with non-empty filter

    • Description: Implement a calculated member in SSAS MDX using the NON EMPTY function for filtering.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( NON EMPTY [Product].[Product].MEMBERS, [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  8. SSAS MDX calculated member with conditional filter

    • Description: Create a calculated member in SSAS MDX with a conditional filter based on a measure value.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Product].[Product].MEMBERS, [Measures].[Sales Amount] > 10000 ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  9. SSAS MDX calculated member with range filter

    • Description: Define a calculated member in SSAS MDX that filters data within a specific range.
    • Code:
      WITH MEMBER [Measures].[FilteredSales] AS 'SUM( FILTER( [Date].[Calendar].MEMBERS, [Date].[Calendar].CurrentMember.MemberValue >= STRTOMEMBER("2003-01-01") AND [Date].[Calendar].CurrentMember.MemberValue <= STRTOMEMBER("2003-12-31") ), [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

  10. SSAS MDX calculated member with set filter

    • Description: Create a calculated member in SSAS MDX that filters data based on a predefined set.
    • Code:
      WITH SET [FilteredProducts] AS 'FILTER( [Product].[Product].MEMBERS, [Product].[Product].CurrentMember.Properties("Category") = "Electronics" )' MEMBER [Measures].[FilteredSales] AS 'SUM( [FilteredProducts], [Measures].[Sales Amount] )' SELECT [Measures].[FilteredSales] ON COLUMNS, [Date].[Calendar Year].[Calendar Year].MEMBERS ON ROWS FROM [Adventure Works]

More Tags

azure-cli monitoring regularized windows-phone-8 plyr ora-01036 weights linear-interpolation font-size gitpython

More Programming Questions

More Math Calculators

More Tax and Salary Calculators

More General chemistry Calculators

More Everyday Utility Calculators