Calculated Member based on a dimension breaks if dimension in query

by

I have a multidimensional cube in SSAS. I’m created a calculated member to get the Contract Value by Contract Num by dividing by row count (not important really).

It all works as long as Contract Num is not in the query. When I add Contract Num to the query, it seems lost everything in the “WHERE” part of the query and give me rows for every Contract Num even though they should be filtered out by the “WHERE”.

Am I doing something wrong? Is this just not allowed? What can I do? Users using the cube will want to use this with Contract Num on grid at times.

This is my query:

WITH 
MEMBER [Measures].[The Value] AS
    SUM( 
    [Contract].[Contract Num].[Contract Num],
    ([Measures].[Contract Value] / [Measures].[The Row Count])
    )

SELECT
    {
    [Measures].[Quantity],
    [Measures].[Amount],
    [Measures].[The Value]} ON COLUMNS,
    NON EMPTY
    (
    [States].[State]
    , [Contract].[Contract Num].[Contract Num]
    ) ON ROWS
FROM
    [TheCube]
WHERE
    (
    [The Date].[Year].[Year].&[2023],
    [The Date].[Month].[Month].&[05],
    [Trans Type].[Trans Type Group].&[Revenue]
    )

Leave a Comment