MDX - Calculated Member - Use Same Value Across All Time Periods

Question

I'm pretty new to MDX, but am stuck supporting a new tool and have a question about a request that recently came my way. We have a cube of prescription data, where prescribers are assigned to different territories. My client wanted a function to calculate the number of prescribers assigned to a territory, so I produced this:

COUNT(
     FILTER([M_Prescriber].[Prescriber].[Prescriber].Members*[Measures].[M-TRx],
     [M_Market_Product].[Product].[All]
     )                              
)

Which gives a count of prescribers who have written a prescription in the time period. The problem the client has is that this function produces a different result in each month because, naturally, some prescribers don't write prescriptions every month. Is there a way to return the number of prescribers assigned to a territory without taking into account whether they have any prescription data in a particular month. Or could I calculate this value in the latest time period and use that value in every time period?

Let me know if I can provide more details.

Where is the territory-prescriber relationship captured in the cube? I mean, is there a territory level on the M_Prescriber dimension, or a separate Territory dimension with the only relationship being the cell values of M-TRx, or something else? — findango
Thanks so much for responding. There is a geography dimension ([M_Geography]) that includes a user-defined hierarchy. Division -> District -> Territory -> Prescriber. Yes, there are 2 prescriber attribtutes, one in the prescriber dimension and one in the geography dimension. Both are mapped to the appropriate territories. — Frank Vinci

findango findango · Accepted Answer · 2013-03-07T18:17:14

OK, if I understand correctly, you want to count the Prescriber members that are in each Territory, and you already have a hierarchy built that describes which Prescribers are in which Territory.

Here's an example against Adventure Works of how you could do it:

WITH MEMBER [Measures].[count] AS 
    Count([Customer].[Customer Geography].CurrentMember.Children)
SELECT
    [Measures].[count] ON COLUMNS,
    DrilldownLevel([Customer].[Customer Geography].[All Customers]) ON ROWS
FROM [Adventure Works];

results:

                count
All Customers       6
Australia           5
Canada              6
France             17
Germany             6
United Kingdom      1
United States      36

This counts only the immediate children of each node (the provinces or states in the hierarchy), so the count for All Customers is not an aggregate of the counts of each country.

In your case, you would change the ROWS selection to be your Division -> District -> Territory level.

Hope that helps at least set you on the right track.

MDX - Calculated Member - Use Same Value Across All Time Periods

1 Answers