Today, I received a special requirement from my client that they wanted to build a report for counting distinct Brand Name of Digital Media Type. For example, a data source as below

And the client expected the output as below

As you saw two above image, the client only wanted to count unique Brand Name of Digital. Other Media Types are excluded (Brand Count is NULL). The number of distinct values for (2017-October, Digital ) is 2 because there are two Brand Name and so on.


There are some approaches for this kind of requirement. We definitely create a calculated field and when we query data for the report, we combine IIF statement to check in case Media Type is Digital or not.

However, I would like to share with you another approach that we use SCOPE statement.


[ MDX_Statement ]

Assuming that I developed a OLAP cube with SSAS and I created a calculated field that named as Brand Distinct Count. The script to create this calculated field, we only count unique BrandName in case Media Type is Digital

CREATE MEMBER CURRENTCUBE.[Measures].[Brand Distinct Count]
AS NULL, /*DISTINCTCOUNT([Brand Name].[Brand Name].[Brand Name]),*/
/*Scope for Digital */
[Media Type].[Media Type].&[Digital]
Measures.[Brand Distinct Count] = DISTINCTCOUNT([Brand Name].[Brand Name].[Brand Name]);

Now, let’s see how we query data with MDX to build the report

SELECT ([Measures].[Brand Distinct Count]) ON COLUMNS,
NON EMPTY([Year Month].[Year Month].[Year Month],
[Media Type].[Media Type].[Media Type],
[Media Sub Type].[Media Sub Type].[Media Sub Type]) ON ROWS
FROM [Brand]


SCOPE is used for many situations and it is quite useful  if you want to work on a sub-cube, that you can not execute with IIF or CASE…WHEN statement because they require input MDX expression. For example, a retail company needs to see how much profit will be impacted if they increase price of some products in some their stores, other stores will still keep current price. In this case, SCOPE is a suitable approach.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.