Calculated Member based on a dimension breaks if dimension in query

Lane Meadows 5 Reputation points
2023-10-25T20:46:11.0833333+00:00

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]
     )

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,282 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2023-10-26T10:25:17.68+00:00

    give me rows for every Contract Num even though they should be filtered out by the "WHERE"

    There's nothing in the Where clause of the query that is directly related to the Contract dimension or its Contract Num attribute. The only thing in your query that filters the rows is the NON EMPTY used for rows, which requires all three measures to be empty for the row to get filtered out.

    Also, double-check whether you actually meant to sum over EXISTING Contract Nums in your measure.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.