Search code examples
ssasmdx

Measures/KPIs based on conditional/filtered counts in SSAS

I'm trying to work out how to define some of the KPI's.
If I may give an example:

Fact Table
ReferenceNo
CountofReferenceNo (Measure)

Dimension
ReferenceNumber
Status
Type

So let's say that one of our official KPI's is a count of reference numbers of a certain type ie type = 'generic'. Is it possible to use an expression to define a KPI Value that is a measure with an added filter? Or would this need to be created as an additional measure? If a measure, then I cannot work out how to add custom calculated measure, nor how to write it if I could!

The equivalent SQL would be:

SELECT COUNT(ReferenceNo)
FROM FactTable INNER JOIN
Dimension ON FactTable.ReferenceNo = Dimension.ReferenceNo
WHERE Dimension.Type = 'generic'

If I could work out this one, then most of our KPI's are similar and are based on counts so I should hopefully be able to go on and work out the rest.

Solution

  • The sql in mdx would be something like:

    SELECT 
   [Measures].[CountofReferenceNo] ON 0 
FROM [YourCube] 
WHERE [DimensionType].[generic];

    Or you can create a tuple in a custom measure inside a WITH clause:

    WITH MEMBER [Measures].[CountofReferenceNo_Generic] AS
(
 [Measures].[CountofReferenceNo]
 ,[DimensionType].[generic]
)
SELECT 
   [Measures].[CountofReferenceNo_Generic] ON 0 
FROM [YourCube];