Gets customers with value greater than X

Question

Post reply

Gets customers with value greater than X

ktflash

Default port

Points: 1400

November 12, 2021 at 10:17 am

#3952327

Hi,

im trying to get something to work and maybe (hopefully) i am just missing some small thing.

I have the following measure that is working as intended, it COUNTS the customers having a certain measure >= X in this case 1000:

CREATE MEMBER CURRENTCUBE.[Measures].[Count customers with Value > 1000] AS 
 AS count(
            filter(
                  [Customer].[Customer ID].[Customer ID].members,
                  Aggregate([Datatype].[Datatype].&[Act],[Measures].[Value]) >= 1000
                  )
       ), 
VISIBLE = 1;

Now INSTEAD of the number of customers i need the customer ids themselves. By adding an element to a dimension.

This is the idea behind what im trying to do.

It works, it a simply links ONE example customer to this attribut.

CREATE MEMBER CURRENTCUBE.[Customer].[Customer ID].[Custom Value > 1000] AS 
[Customer].[Customer ID].&[00000000], 
VISIBLE = 1;

What i need is to use the logic of the measure that is counting the customers with a value higher than X, but instead just returns the customers themselves without the count. I tried the following versions but could not get it to work.

Its bascically using the "one Example" version with the reduced logic of "count" measure.

CREATE MEMBER CURRENTCUBE.[Customer].[Customer ID].[Customers with Value > 1000] AS 
filter(
[Customer].[Customer ID].[Customer ID].members,
Aggregate([Datatype].[Datatype].&[Act],[Measures].[Value]) >= 1000), 
VISIBLE = 1;



--Filter shows as intended in the select but i have no idea how to convert this to a dimension attribut 
--containting all the custmer ids from the filter
SELECT filter(
[Customer].[Customer ID].[Customer ID].Members,
Aggregate([Datatype].[Datatype].&[Act],[Measures].[Value]) >= 1000
   )
       ON 1,
       {[Measures].[Value]}
       ON 0
FROM [Vertrieb]

CREATE MEMBER CURRENTCUBE.[Customer].[Customer ID].[Customers with Value > 1000] AS 
filter(
[Customer].[Customer ID].[Customer ID],
Aggregate([Datatype].[Datatype].&[Act],[Measures].[Value]) >= 1000
   ), 
VISIBLE = 1;

Maybe anyone knows what my mistake is in the 2 attempts i made.

I hope/assume its just some small adjustment to actually make it work since the measure already does what its intended to do.

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

December 2, 2021 at 12:09 pm

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80332 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

ktflash Default port Points: 1400 More actions · Answer 2

Ok issue is member can only return a single element, instead one must use sets:

CREATE Set CURRENTCUBE.[Customer].[Customer ID].[Customers with Value > 1000] AS 
filter(
       [Customer].[Customer ID].[Customer ID],
       Aggregate([Datatype].[Datatype].&[Act],[Measures].[Value]) >= 1000
   ), 
VISIBLE = 1;

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Gets customers with value greater than X

Cookies on SQLServerCentral