CODEWITHSUNDEEP
ssasmdx
king conch
king conch

Reputation: 337

Filter by a measure value

Is it possible to filter by a measure value? In my scenario, I need to limit the Widget population by [Measures].[Returned Widget Count] that is a 1 or 0, to only where the Measure = 1. For example something like this:

    Select  {
            (
            [Dim Widget].[Build Site].[The Moon],
            [Dim Date].[Build Day].[2012-04-24],
            [Measures].[Widget Count]
            ),
            (
            [Dim Widget].[Build Site].[The Moon],
            [Dim Date].[Build Day].[2012-04-24],
            [Measures].[Returned Widget Count]
            )
            }   on 0
    from    (
            Select  [Dim Widget].[Build Site] on 0
            From    [Widgetizer]
            Where   Filter(
                    [Dim Widget].[Serial Number].Members,
                    [Measures].[Returned Widget Count].Value > 0
                    )
            )

The query executes, but it is not filtereing my population. I know this because it returns the same result as:

    Select  {
            (
            [Dim Widget].[Build Site].[The Moon],
            [Dim Date].[Build Day].[2012-04-24],
            [Measures].[Widget Count]
            ),
            (
            [Dim Widget].[Build Site].[The Moon],
            [Dim Date].[Build Day].[2012-04-24],
            [Measures].[Returned Widget Count]
            )
            }   on 0
    from    [Widgetizer]

Where the Filter function has not even been applied.

How can I reduce the population to ONLY where a measure is a specific value?

Upvotes: 3

Views: 2000

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

I think Filter is working properly. The issue might be with the content of the hierarchy (?) [Dim Widget].[Serial Number] - if you have a [All] member than the following filter is going to select it as well :

Filter( [Dim Widget].[Serial Number].Members, [Measures].[Returned Widget Count].Value > 0 )

meaning the sub-select is doing nothing. How about writing :

Filter( [Dim Widget].[Serial Number].Members, [Measures].[Returned Widget Count].Value = 1 )

Upvotes: 2

Related Questions