CODEWITHSUNDEEP
ssasmdxolap
TerrorBight
TerrorBight

Reputation: 313

MDX Filter based on a Dimension field and Measure field not returning expected result

I'm using SSAS 2019 and have the following MDX query to determine data errors - i.e. in this case where a given score ([Measures].[Score]) is above what the maximum score ([PerformanceLevel].[MaxScore]) is

SELECT NON EMPTY { 
    [Measures].[FactTestScoreCount]
} ON COLUMNS, 
NON EMPTY 
{(
    [TestEvent].[Key].[Key].Members 
)} ON ROWS 
FROM [TestScore]
WHERE 
(
    FILTER(
        [PerformanceLevel].[MaxScore].[MaxScore].Members,
        [PerformanceLevel].[MaxScore].CurrentMember.MemberValue < [Measures].[Score]
    )
)

...you'll note it uses a Measure value to compare against a Dimension value in the filter

However the above query incorrectly returns all rows (it should return zero rows as we have no scores in the current database that are above the maximum score)

I have tried using a HAVING and also tried to add the filter into the FROM however I get the same result. I have also tried converting the data-type to int on both sides of the expression but to no avail. I tried temporarily changing the condition to hard-code (numeric) values and this seems to narrow down the issue as being the [Measures].[Score] in the FILTER - i.e. as far as I can see putting the Measure within the FILTER doesn't seem to be working as expected but I can't work out why

Any ideas would be much appreciated

Upvotes: 0

Views: 231

Answers (1)

TerrorBight
TerrorBight

Reputation: 313

I didn't fully understand what the first parameter of the Filter function represented - specifically the granularity, otherwise it (depending on your aggregation type will SUM the measure). Also moving the Filter up to the select allows more flexibility when joining/displaying rows - so I came up with the below which works well

SELECT NON EMPTY { 
  [Measures].[FactTestScoreCount]
} ON COLUMNS, 
Filter(
  NonEmpty(
    {(
      [TestEvent].[Key].[Key].members
    )}
  ), [Measures].[Score] > [PerformanceLevel.[MaxScore].CurrentMember.MemberValue 
) ON ROWS
FROM [TestScore]

Upvotes: 0

Related Questions