MDX calculation for count of sum within a range

2
votes

I have a cube in SSAS with the following measures:

NameID
Number
Date

NameID points to a Person dimension with a Name string (there can be multiple records with the same NameID). Date points to a server-side date dimension.

I'm looking for an MDX calculation I can include in the cube that will show the count of Person.Names where the sum of the Measure.Number fields for the date range given in the query is within a certain range (say 0-10, 11-20, etc).

I've tried various flavors of COUNT(), FILTER() and SUM(), but my MDX is rusty, and I'm missing something.

1
ssasmdx
It sounds like you need discretization. Is this what you're trying to do? databasejournal.com/features/mssql/article.php/3809096/… – Jon Crowell

1 Answers

1
votes

MDX has no histogram features, we're thinking about adding this in icCube, but at this point in time there is nothing out of the box in MDX. You can read this article for a solution in SSAS.

Besides you can create a calculated member with count and filter, but I believe you already did this.

The problem defining this in a hierarchy is that it won't work as expected with filters. Imagine you wan't this ranges for a particular year. But if your ranges are fixed, creating a dimension/hierarchy might be also solution.