Search code examples
ssasmdx

Analysis services Filter function implicitly filters empty elements

I need to filter members from [__Account.Account selection] by some condition regardless if members are empty or not, but Filter() function implicitly excludes empty members. Is this is a bug or a feature? MSDN does not mention such behavior for Filter function.

Any idea how to avoid the issue?

WITH 
  SET [__Account.Account selection] AS 
    '{
      {
        [Account].[Account Number].&[110]
       ,[Account].[Account Number].&[1130]
       ,[Account].[Account Number].&[1164]
       ,[Account].[Account Number].&[1210]
       ,[Account].[Account Number].&[1300]
       ,[Account].[Account Number].&[20]
       ,[Account].[Account Number].&[8500]
       ,[Account].[Account Number].&[8040]
      }
    }' 
  SET [__Account.Account Number_RootMembers_Smart] AS 
    '{
    Filter(
        [__Account.Account selection],
        1 = 1)}' 
SELECT 
  [__Account.Account Number_RootMembers_Smart] ON ROWS
 ,{} ON COLUMNS
FROM [Adventure Works]

NOTE: Function Generate() has the same behavior.

NOTE2: By "empty member" I mean member with not value on any measure.

Response

And there are members with measures... enter image description here

Solution

  • Please try this:

    WITH 
  SET [__Account.Account selection] AS 
    {
      {
        [Account].[Account Number].&[110]
       ,[Account].[Account Number].&[1130]
       ,[Account].[Account Number].&[1164]
       ,[Account].[Account Number].&[1210]
       ,[Account].[Account Number].&[1300]
       ,[Account].[Account Number].&[20]
       ,[Account].[Account Number].&[8500]
       ,[Account].[Account Number].&[8040]
      }
      * [Account].[Account].[Account].Members
    } 
  SET [__Account.Account Number_RootMembers_Smart] AS 
    {
    Filter(
        [__Account.Account selection],
        1=1)}
SELECT  {} ON COLUMNS,

  [__Account.Account Number_RootMembers_Smart] ON ROWS
FROM [Adventure Works]

    Notice that I added the [Account].[Account].[Account].Members into the query. Previously since it was not mentioned, the current coordinate was the [All Accounts] member. Since the Account dimension is a parent-child dimension, the All member doesn't exist with 6 of 8 account numbers apparently. Changing the query ensures that the relevant Account and Account Number pair get put together so that all 8 rows exist in the cube space and show up.