# Set iteration in calculation - 2 views of CurrentMember

### Question

• Hi

I am trying to define a calculation that sums values over time based on a member attribute and am having some difficulty understanding how to reference the "CurrentMember" of the time dimension in my query separate from the "CurrentMember" whilst iterating over the values in a set. The following MDX which sums all the days which have a certain week number explains the problem in a simplified way. You will see that I have defined the query to have the date &[1521] on the rows of the query and it is also defined in the Filter function in my calculation.

WITH MEMBER [Measures].[SameWeek] AS

Sum

(

Filter

(

[Date].[Fiscal Year].[Fiscal Day].Members

,

[Date].[Fiscal Year].CurrentMember.Properties("Week Number Fiscal")  /* This is the current member in the set */

=

[Date].[Fiscal Year].[Fiscal Day].&[1521].Properties("Week Number Fiscal")

)

,

[Measures].[Journey - Count]

)

SELECT

{[Measures].[Journey - Count], [Measures].[SameWeek]} ON COLUMNS,

{[Date].[Fiscal Year].[Fiscal Day].&[1521]} ON ROWS

FROM [Journey]

What I really want to do is to reference [Date - Actual].[Fiscal Year - Month - Day].[Fiscal Day].CurrentMember in the filter for the current member of time in the row axis so that the calculation definition is not for a single date so I would have something like:

WITH MEMBER [Measures].[SameWeek] AS

Sum

(

Filter

(

[Date].[Fiscal Year].[Fiscal Day].Members

,

[Date].[Fiscal Year].CurrentMember.Properties("Week Number Fiscal") /* This is the current member in the set */

=

[Date].[Fiscal Year].TheCurrentMemberOfTimeOnTheRowsOfMyQuery.Properties("Week Number Fiscal")

)

,

[Measures].[Journey - Count]

)

SELECT

{[Measures].[Journey - Count], [Measures].[SameWeek]} ON COLUMNS,

{[Date].[Fiscal Year].[Fiscal Day].&[1521]} ON ROWS

FROM [Journey]

Any thoughts??

Andrew Wiles - www.it-workplace.com - MDX made simple
Tuesday, September 14, 2010 10:15 AM

• Well,

it looks like a classical problem with two variables that need to switch their places. If we disregard the XOR trick for the moment, the way to solve the problem is by introducing another variable that will serve the purpose of a temporary container.

Let's make an overview of possible scenarios and practice the way of thinking how to solve problems of this kind.

The initial idea that pops up is to introduce another calculated measure because calculated measures are evaluated per cell (row in this case) unless their definition is deterministic. That would suit us. I mean, we need to make comparison per rows by storing the member property value of the current member on rows. However, there's a problem in this logic. We cannot make a comparison using that measure, it won't work and here's why. A measure is evaluated only when referred to. In this case - inside the filter loop. So, once the loop sets its own context we automatically lose the outer context set by the hierarchy on rows which means we can not make a comparison, we'd be testing if x = x. Meaning, this approach is a dead-end.

Not everything was in vain. That experiment led us to thinking that the definition should be placed outside the loop. What I mean by that is that we should evaluate the current member on rows BEFORE we go inside the loop, that's the only way to get it right. Besides evaluating, we should also store it somehow, somewhere.

We could store it in a set for instance. For example, we can define the set using a single member, that is, the current member of the Fiscal Year hierarchy. But we'd fail again. Set is evaluated before the iteration on rows begins. Current member at that moment is the default member of that hierarchy. Moreover, it won't change per cell once we start iterating on members on rows. Another dead-end.

So, it's not a measure, it's not a set. What's left? Is there a solution to this problem?

Yes, there is. But it's the most obscure one.

The trick is to literally define a named set inside the calculation itself, before the loop. That can be achieved using the, so called, set alias . In this case, it's actually a dummy part of the expression serving the purpose to persist a set in a variable, that is, to define it as a set alias and to be able to use it in subsequent expressions. Truth be told, it's something I learned long ago from Chris Webb and his blog posts.

Here's the working solution, modified a bit to work on Adventure Works 2008 R2 database (and to show the change per week).

```WITH
MEMBER
[Measures].[SameWeek] AS
null * Count( [Date].[Fiscal].CurrentMember as MySet1 )
+
Sum (
Filter
(
[Date].[Fiscal].[Date].Members as MySet2,
MySet2.Current.Properties("Fiscal Week")
=
MySet1.Item(0).Properties("Fiscal Week")
),
[Measures].[Internet Order Count]
)
SELECT
{
[Measures].[Internet Order Count], [Measures].[SameWeek]
}
ON AXIS(0),
{
LastPeriods(14, [Date].[Fiscal].[Date].&[20060914])
}
ON AXIS(1)
FROM
```

That's 10th and 11th fiscal week of the year 2006 if you want to test it using the Fiscal Weeks user hierarchy. The values returned are 33 and 63 for those weeks, respectively.

Nice?

Tomislav Piasevoli
www.softpro.hr

• Marked as answer by Wednesday, September 15, 2010 6:20 AM
Tuesday, September 14, 2010 3:15 PM

### All replies

• A small update - I was hopeful that the expression:

Axis(1).Item(0).Hierarchy.CurrentMember

would solve my problem but it appears this expression will evaluate relative to the set in the Filter function rather than in the query.

Still looking......

Andrew Wiles - www.it-workplace.com - MDX made simple
Tuesday, September 14, 2010 12:28 PM
• Well,

it looks like a classical problem with two variables that need to switch their places. If we disregard the XOR trick for the moment, the way to solve the problem is by introducing another variable that will serve the purpose of a temporary container.

Let's make an overview of possible scenarios and practice the way of thinking how to solve problems of this kind.

The initial idea that pops up is to introduce another calculated measure because calculated measures are evaluated per cell (row in this case) unless their definition is deterministic. That would suit us. I mean, we need to make comparison per rows by storing the member property value of the current member on rows. However, there's a problem in this logic. We cannot make a comparison using that measure, it won't work and here's why. A measure is evaluated only when referred to. In this case - inside the filter loop. So, once the loop sets its own context we automatically lose the outer context set by the hierarchy on rows which means we can not make a comparison, we'd be testing if x = x. Meaning, this approach is a dead-end.

Not everything was in vain. That experiment led us to thinking that the definition should be placed outside the loop. What I mean by that is that we should evaluate the current member on rows BEFORE we go inside the loop, that's the only way to get it right. Besides evaluating, we should also store it somehow, somewhere.

We could store it in a set for instance. For example, we can define the set using a single member, that is, the current member of the Fiscal Year hierarchy. But we'd fail again. Set is evaluated before the iteration on rows begins. Current member at that moment is the default member of that hierarchy. Moreover, it won't change per cell once we start iterating on members on rows. Another dead-end.

So, it's not a measure, it's not a set. What's left? Is there a solution to this problem?

Yes, there is. But it's the most obscure one.

The trick is to literally define a named set inside the calculation itself, before the loop. That can be achieved using the, so called, set alias . In this case, it's actually a dummy part of the expression serving the purpose to persist a set in a variable, that is, to define it as a set alias and to be able to use it in subsequent expressions. Truth be told, it's something I learned long ago from Chris Webb and his blog posts.

Here's the working solution, modified a bit to work on Adventure Works 2008 R2 database (and to show the change per week).

```WITH
MEMBER
[Measures].[SameWeek] AS
null * Count( [Date].[Fiscal].CurrentMember as MySet1 )
+
Sum (
Filter
(
[Date].[Fiscal].[Date].Members as MySet2,
MySet2.Current.Properties("Fiscal Week")
=
MySet1.Item(0).Properties("Fiscal Week")
),
[Measures].[Internet Order Count]
)
SELECT
{
[Measures].[Internet Order Count], [Measures].[SameWeek]
}
ON AXIS(0),
{
LastPeriods(14, [Date].[Fiscal].[Date].&[20060914])
}
ON AXIS(1)
FROM
```

That's 10th and 11th fiscal week of the year 2006 if you want to test it using the Fiscal Weeks user hierarchy. The values returned are 33 and 63 for those weeks, respectively.

Nice?

Tomislav Piasevoli