Gets customers with value greater than X

  • 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.

     

     

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

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

  • 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;
  • This was removed by the editor as SPAM

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

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