CODEWITHSUNDEEP
sqlsql-server-2008-r2ssasmdx
Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

How can I filter Fact with a Dimension?

I use SSAS and SQL Server 2008 R2

I write this query and it works:

SELECT 
 [Measures].[Circulation Count] 
    ON COLUMNS 
FROM [Store]
where [Circulation Kind].[Circulation Code].&[1]

And result is : 362154.2154

But when I use Filter function my result is empty.

SELECT 
Filter(
    [Measures].[Circulation Count] 
         ,
    [Circulation Kind].[Circulation Code]
    = 
    [Circulation Kind].[Circulation Code].&[1]
)
    ON COLUMNS 
FROM [Store]

How can I filter my result with the Filter function?

Upvotes: 1

Views: 168

Answers (2)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

After communication with @FrankPI i fond that is good :

With 
    Member X As 
       ( 
          [Measures].[Circulation Count] , 
          (
              [Circulation Kind].[Circulation Code].&[1]
          ) 
       )

Select 
    X On Columns
From <>

And in this method i can filter my measure with a dimension.

Upvotes: 0

FrankPl
FrankPl

Reputation: 13315

What your Filter expression does is:

  • Starting with the one element set consisting of the measure [Measures].[Circulation Count].
  • Then, from this set keep (= filter) the elements (= measures) where the value of [Circulation Kind].[Circulation Code] is the same as the value of [Circulation Kind].[Circulation Code].&[1]. Comparing an attribute and a member triggers an implicit type cast to make both the same type, in this case the attribute is converted to a member by implicitly applying the DefaultMember method. This means you compare the value of the default member (usually the All member) with the value of the member having key 1. And, assuming there are more than one members in this attribute, this is probably always false.

Note that = compares the values on both sides, in this case, as the context sets the measure to Circulation Count, the value of Circulation Count. If you want to compare for member identity, you would have to use IS instead of =.

WHat does this mean for your statement? I am not sure why you do not want to use the WHERE clause, but as @mmarie stated, you could just move the [Circulation Code] from the WHERE axis to the rows axis:

SELECT 
 [Measures].[Circulation Count] 
    ON COLUMNS,
 [Circulation Kind].[Circulation Code].&[1]
    ON ROWS
FROM [Store]

And if you really, really want to use Filter, you could use:

SELECT 
 [Measures].[Circulation Count] 
    ON COLUMNS,
 Filter([Circulation Kind].[Circulation Code].Members as c,
        c.Current IS [Circulation Kind].[Circulation Code].&[1]
       )
    ON ROWS
FROM [Store]

Note the usage of .Members to explicitly convert the attribute to the set of its members, and the usage of IS, not =, in the last statement.

Upvotes: 1

Related Questions