Search code examples
ssasaggregate

Row aggregation of count-distinct measure

I have a fairly simple project set up to demonstrate what I want here. Here's the data:

Group

ID  Name
 1  Group 1
 2  Group 2
 3  Group 3

Person

ID  GroupID  Age  Name
 1        1   18  John
 2        1   21  Stephen
 3        1   18  Kate
 4        2   18  Mary
 5        2   19  Joseph
 6        2   19  Michael
 7        3   21  David
 8        3   22  Kevin
 9        3   21  Julian

I have 1 measure in my cube called Person Count which is a Distinct count on Person ID
I have set up each non-ID column in the dimensions as attributes (Age, Person Name, Group).

When I process and browse the cube in Business Intelligence Development Studio, I get the following result set:

Actual Results

But what I actually want here are the rows for Age to aggregate up the count of the Person Count together, so here it should show 2 and only one row for 18.

Required Results

Is this possible (and how)?

Solution

  • Turns out this was a problem with the way I set up the Age attribute for the dimension.
    I had:
    KeyColumns = Person.ID
    ValueColumn = Person.Age.

    I don't know why I did this, but the solution is to delete the content of ValueColumn and set the KeyColumns to Person.Age again.

    I now get the following result:

    Final result

    Everything else is the same for the project; this was the only change and is exactly what I wanted. If I get any issues with it I will keep this post updated for anyone else who may run into this in the future.