none
Filter dimension based on Measure in MDX RRS feed

  • Question

  • Hi Experts,

    I am here with a new query...!!

    I want to write a mdx expression in cube calculation designer which will calculate the below logic -

    I have a dimension Product. There is a measure coming from fact called sales amount. I want to calculate the count of products which are poor performing (whose sales amount is between 0 to 1000 Rs). Also, I want this measure to be filtered based on date and location. So that when I drag region, month and '# of poor performing products' in the excel, it should give me the number of products whose sales amount has been between 1 to 1000 in respective month and Region.

    Please let me know the mdx expression for the same.

    Thanks,

    Ruchika


    • Edited by RuchikaG Friday, February 10, 2017 3:55 AM
    Friday, February 10, 2017 3:50 AM

All replies

  • Hi Ruchika,

    Thanks for your question.
    You may try following MDX expression:
    Create Member CurrentCube.[Measures].[Poor Performing Product Count] AS 
    exists({[Product].[Product].[Product]}, filter([Product].[Product].[Product]*
                                                        [Date].[Month].[Month]*
                                                    [Region].[RegionID].[RegionID],
               [Measure].[Sales Amount]>=0 and [Measure].[Sales Amount]<=1000)).COUNT


    See my sample MDX query below:
    WITH
    SET [GROUP_A] AS
    exists({[Customer].[Customer].[Customer]},
    filter([Customer].[Customer].[Customer]*
    [Date].[Calendar Year].&[2008].LastSibling,
    [Measures].[Internet Sales Amount]> 200
    ))
    SELECT [GROUP_A] ON COLUMNS,
           [Measures].[Internet Sales Amount]ON ROWS
    FROM [Adventure Works];



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Friday, February 10, 2017 12:41 PM
    Friday, February 10, 2017 8:25 AM
    Moderator