CODEWITHSUNDEEP
mdxdata-warehousemondrian
Damian_Orly
Damian_Orly

Reputation: 27

MDX calculated member error

How can make a calculated member ? I get this error Mondrian Error:Member expression 'Filter(Descendants([User].[User Name].Members), ([User].[User Name].CurrentMember.Properties("location") = 'foo')) must not be a set

WITH MEMBER [Measures].[some users] AS
Filter(
  Descendants([User].[User Name].Members), 
  [User].[User Name].CurrentMember.Properties("location") = 'foo'
 )  
)

SELECT
 NON EMPTY {[Measures].[some users]} ON COLUMNS,
 NON EMPTY CrossJoin(
   [Date].[Date].Members, 
   [Customer].[Customer Name].Members
  ) ON ROWS

FROM [Users]

Upvotes: 0

Views: 841

Answers (1)

whytheq
whytheq

Reputation: 35557

FILTER returns a SET and you've tried to assign it to a MEMBER in the WITH clause.

This should work but no measure is specified so you'll get the default measure:

SELECT
 NON EMPTY 
{Filter(
  Descendants([User].[User Name].Members), 
  [User].[User Name].CurrentMember.Properties("location") = 'foo'
 ) } 
ON COLUMNS,
 NON EMPTY CrossJoin(
   [Date].[Date].Members, 
   [Customer].[Customer Name].Members
  ) ON ROWS

FROM [Users];

You could decide on a measure in the WHERE clause:

SELECT
 NON EMPTY 
{Filter(
  Descendants([User].[User Name].Members), 
  [User].[User Name].CurrentMember.Properties("location") = 'foo'
 ) } 
ON COLUMNS,
 NON EMPTY CrossJoin(
   [Date].[Date].Members, 
   [Customer].[Customer Name].Members
  ) ON ROWS

FROM [Users]
WHERE [Measures].[aMeasureThatFitsContext];

Example of using RANK. Just a bit unsure how this fits in with you original script?

WITH 
  SET [myFilteredSet] AS 
    Filter(
      Descendants([User].[User Name].Members), 
      [User].[User Name].CurrentMember.Properties("location") = 'foo'
    )
  MEMBER [Measures].[userRank] AS
    RANK(
      [User].[User Name].CURRENTMEMBER,
      [myFilteredSet],
      [Measures].[EXISTING_MEASURE] //<<this is the measure you are ordering by  
    ) 
SELECT
 NON EMPTY 
   {[Measures].[EXISTING_MEASURE],[Measures].[userRank] } 
 ON COLUMNS,
 NON EMPTY 
   [myFilteredSet] 
 ON ROWS
FROM [Users]

Upvotes: 1

Related Questions