Post reply

Using count() and crossjoin() to return distinct counts

  • May 12, 2005 at 7:44 pm

    #164388

    In my AS cube, I've created a calculated measure using the following MDX:

    Count(Crossjoin({[Measures].[Number of hits]},

    Descendants([Area].Currentmember, [Area].[User Name])),

    Excludeempty)

    As expected, the measure provides the number of unique users for each area visited (based on logs from a web site). The problem I'm having is that the total number of unique users for ALL areas visited appears to be the sum of the

    unique users from each area, rather than simply being the total number of unique users. Anyone have any suggestions on how to get the cube to calculate correctly at the ALL level?

  • May 12, 2005 at 8:55 pm

    #558596

    Not sure if it will work syntactically, but you could try putting a distinct in after the comma but before the descendants.  Distinct works at the begining of a row/col statement but haven't tried it 'in-line'. 

    Steve.

  • May 13, 2005 at 1:55 pm

    #558839

    Thanks for the reply, Steve. Unfortunately, this didn't work. I also tried different variations of this, but with no luck.

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

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