Using a distinct count measure to implement histograms over existing hierarchies

Histograms are an important tool in data analysis. They represent the distribution of frequencies of an entity or event. In OLAP, those terms translate to dimensions and their attributes.

This recipe illustrates how to implement a histogram over an attribute Color. The histogram needs to tell us the product count for each Color in each Fiscal Year.

In order to create a histogram, we need a measure that can count distinct members of an attribute for any given context.

There are two solutions to this problem. One is to use a calculated measure; the other is to use a regular measure with the distinct count type of the aggregation.

Many BI developers might lean towards the ...

Get MDX with Microsoft SQL Server 2016 Analysis Services Cookbook - Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.