TOPN using SSAS MD

Hi parry2k

You should use :

- first of all a technical dimension to list the N value you want for your top N (1..n). Let's call it Top N

declare a default value for your Top N attribute ( for example 10)

- use dynamic set to implement you top depends on your Top N attribute :

CREATE DYNAMIC SET CURRENTCUBE.[Top N Products]
 AS TopCount
    (
       [Product].[Product Name].[Product Name].MEMBERS,
       [Top N].[Top N].currentMember.member_caption,
       [Measures].[Sales Amount]
    ), DISPLAY_FOLDER = 'Named Sets';  

Unfortunately PBI doesn't expose MDX Sets so you have to create a calculated measure to identify in my example each product name existing in your Top N :

CREATE MEMBER CURRENTCUBE.[Measures].[Is Top N Product]
AS NULL;

SCOPE ([Product].[Product Name].[Product Name],[Measures].[Is Top N Product]);
THIS = IIF(COUNT(EXISTING EXISTS([Product].[Product Name].currentMember,[Top N Products])) > 0, 1, NULL);
END SCOPE;

Open you PBI Desktop, connect to you MD Cube in Direct Connect, put your Top N attribute as a slicer, your product name as a table and add a filter on [Is Top N Product] = 1