We will begin creating our query with a focus on returning results in the same general formation as the Data View we left in the Cube Editor. We will retrieve the Warehouse Profit and Product Count measures, as pictured in Figure 13 above. Next, we will attempt to add a calculated measure that we craft directly in MDX, to replicate the distinct count information we obtained with the Product Count measure that we created in Analysis Manager earlier.
1. Create the following new query:
— SSP10-1 Initial Attempt at Distinction
WITH MEMBER
[MEASURES].[ProdCount]
AS
‘DISTINCTCOUNT({[Product].MEMBERS})’
SELECT
{ [MEASURES].[Warehouse Profit], [MEASURES].[Product Count],
[MEASURES].[ProdCount] } ON COLUMNS,
{[Product].CHILDREN} ON ROWS
FROM
[Warehouse]
The above represents an attempt to meet the information consumers’ objectives with what appears to be the straightforward use of the DISTINCTCOUNT() function. This might represent an approach that seems intuitive to a practitioner who has given up on the handful of non-working or nebulous examples that can be found on the web, (and which happen to be about all we seem to have as a basis for learning MDX, in many instances). While it ultimately fails to provide the desired solution, as we shall see, it should not be surprising that we might attempt this, given the definition in the Books Online, not to mention the words used in the name of the function itself. (Most will agree, also, that it is better to attempt it now, than when under the gun of an employer or a hurried client.)
The calculated member ProdCount embodies the function. I named it ProdCount to distinguish if from Product Count, the measure we created while within the user interface in the earlier section, which I have also decided to present within the results dataset for comparison purposes. Warehouse Profit is also presented to align with our Data View as we left it in the last section.
2. Execute the query using the Run Query button.
The results dataset appears as shown in Figure 16.
Figure 16: The Results Dataset – DISTINCTCOUNT() Approach
3. Save the query as SSP10-1.
It doesn’t require a huge leap of logic to conclude that the ProdCount calculated measure is generating a transaction count, which is probably correctly “distinct,” within its own (actual) meaning, but not at all what the information consumers have requested in our practice example.
Having seen why the “intuitive” approach is lacking, let’s resort to another, more cumbersome approach, which results in the distinct product values that we seek.
4. Create the following new query:
— SSP10-2 Distinction at its Finest
WITH MEMBER
[MEASURES].[CalcCount]
AS
‘COUNT(CROSSJOIN({[MEASURES].[Warehouse Profit]},
DESCENDANTS([Product].CURRENTMEMBER,
[Product].[Product Name])), EXCLUDEEMPTY)’
SELECT
{[MEASURES]. [Warehouse Profit], [MEASURES].[Product Count],
[MEASURES].[CalcCount] } ON COLUMNS,
[Product].CHILDREN ON ROWS
FROM
[Warehouse]
The above attempt at distinction is embodied by the calculated measure CalcCount, named, again, simply as a means of distinguishing it from the measure we created in the Cube Editor, and which we include once again for comparison purposes.
The above approach may not have been the initial impulse that many of us had in tackling what seemed to be a straightforward replication of the Data View we saw earlier. What we are doing, in short, with the CrossJoin() function is marrying the Warehouse Profit values with the products, and returning (thanks to EXCLUDEEMPTY) a count of the non-empty pairings. The Descendants() function builds in flexibility, allowing us to apply the logic equally well to a group of products as to the full set of products. The key to this is the selection of the current member’s descendents, adding the “relativity” that so pointedly underscores the power of the .CurrentMember function.