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]),*/
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Fact Brand’ ;
/*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
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.