I have created a calculated member that assigns customer type. The problem is that it shows every single member from customer dimension and I have too little experience to fix it.
I want to show only customers that actualy ordered. e.g. [Measures].[Total Amount]>0 for currently selected period. e.g. current year. ([Basic Calendar].[Year - Week - Date])
Please advise and see formula below.
CREATE MEMBER CURRENTCUBE.[Measures].[Customer Type]
AS IIF( [Measures].[Orders by Customer]=0, "Zero Customer",
IIF( [Measures].[Orders by Customer]=1, "One Off Customer",
IIF([Measures].[Orders by Customer]>1 and [Measures].[Orders by Customer]<4 , "2 to 3 Orders",
IIF([Measures].[Orders by Customer]>3 and [Measures].[Orders by Customer]<6, "4 to 5 Orders",
IIF([Measures].[Orders by Customer]>5, "VIP Customer", "Other"))))),
VISIBLE = 1 ;
Many thanks
Just add an 'empty' condition that returns null:
CREATE MEMBER CURRENTCUBE.[Measures].[Customer Type]
AS
IIF( IsEmpty([Measures].[Orders by Customer]), null,
IIF([Measures].[Orders by Customer]=0, "Zero Customer",
IIF( [Measures].[Orders by Customer]=1, "One Off Customer",
IIF([Measures].[Orders by Customer]>1 and [Measures].[Orders by Customer]<4 , "2 to 3 Orders",
IIF([Measures].[Orders by Customer]>3 and [Measures].[Orders by Customer]<6, "4 to 5 Orders",
IIF([Measures].[Orders by Customer]>5, "VIP Customer", "Other")))))),
VISIBLE = 1 ;