Calculated Measure returns unfiltered totals when dimension is filtered

Question

IN AS 2008 R2, I have this calculated measure that works fine in all cases, except when the user filters on the ([Resources].[ResourcesByDepartment] dimension.  When they do they, the subtotals and grand totals still return the unfiltered totals as before.

Any suggestion would be greatly appreciated.  I have looked through the forum and tried quite a few things, but couldn't seem to get this fixed.

--------------------------------------------------------------------------

CREATE MEMBER CURRENTCUBE.[MEASURES].[Missing Time] AS
IIF(Isleaf([Resources].[ResourcesByDepartment].CurrentMember),
    IIF([Resources].[ResourcesByDepartment].CurrentMember.Properties("Resource Status")="INACTIVE" 
        OR Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],
        NULL,
        [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])  ,
SUM([Resources].[ResourcesByDepartment].CurrentMember.Children, [MEASURES].[Missing Time])

--------------------------------------------------------------------------

Thanks,

John

Answer 1

Try using EXISTING keyword SUM(EXISTING(SET),MEASURE1)

---

vinu

Answer 2

Thanks, but that didn't make any difference.   Any other ideas?  I am starting to wonder if it's because I am using CurrentMember.Properties("Resource Status") in the IIF...

CREATE MEMBER CURRENTCUBE.[MEASURES].[Missing Time 2] AS
IIF(Isleaf([Resources].[ResourcesByDepartment].CurrentMember),
    IIF([Resources].[ResourcesByDepartment].CurrentMember.Properties("Resource Status")="INACTIVE" 
        OR Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],
        NULL,
        [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])  ,
SUM(Existing([Resources].[ResourcesByDepartment].CurrentMember.Children), [MEASURES].[Missing Time 2])
)

Update:   No change in the behavior even after I take out Resources].[ResourcesByDepartment].CurrentMember.Properties("Resource Status")="INACTIVE" ...  SO the following still doesn't total correctly when the dimension has a filter on it.    This shouldn't be hard to accomplish, right?  :)

CREATE MEMBER CURRENTCUBE.[MEASURES].[Missing Time 2] AS
IIF(Isleaf([Resources].[ResourcesByDepartment].CurrentMember),
    IIF(Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],
        NULL,
        [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])  ,
SUM(Existing([Resources].[ResourcesByDepartment].CurrentMember.Children), [MEASURES].[Missing Time 2])
)

Answer 3

Hi John,

Here you can use  SCOPE, please refer to this similar thread http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3a343014-8412-4d0b-9d62-e129951a0537/ 

Thanks,

---

Challen Fu

TechNet Community Support

Answer 4

Hi John

I think the issue here is that when you have the [Resource by department] selected with more than one member it can't actually resolve the CurrentMember.Children, and so assumes it is the all member. I have had the same issue a few times with no luck. You can look at the following thread and see what i mean

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/cb5b537a-12dc-4329-971b-ae6958b6221b

Answer 5

THanks Challen.   I am pretty sure that I have tried SCOPE too, but I will take a look at your link and try again.

Michael - that sort of makes sense, but we had this same calculation work perfectly in AS 2000.   So I would think AS 2008 R2 can too.

Answer 6

Hi John,

Here you can use  SCOPE, please refer to this similar thread http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/3a343014-8412-4d0b-9d62-e129951a0537/ 

Thanks,

---

Challen Fu

TechNet Community Support

I tried the SCOPE Method in that blog (pretty helpful read actually, thanks), but it didn't work.  In fact, the blogger said it as much.  Scope doesn't return correct totals when filtered. The alternative that he proposed was VISUALTOTALS (at the end of the blog).   I tried that too, but got ony partially there.  The following code will give me the correct filtered totals at the Grand Total (ALL) level, but not any of the hierachies.   I tried adding other cases to the case statement, but got #Value! in Excel.

--------------------------------THIS Gets me the correct grand totals---------------------------------

CASE
WHEN
[Resources].[ResourcesByDepartment].currentmember is [Resources].[ResourcesByDepartment].[ALL]
THEN
  SUM(
    except(VisualTotals(axis(1)), [Resources].[ResourcesByDepartment].[All]),
    IIF( Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],NULL, [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])
    )
ELSE
    IIF( Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],NULL, [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])
END

--------------------------------THIS Gets me the correct grand totals, but the department subtotals return #Value!---------------------------------

CASE
WHEN
[Resources].[ResourcesByDepartment].currentmember is [Resources].[ResourcesByDepartment].[ALL]
THEN
  SUM(
    except(VisualTotals(axis(1)), [Resources].[ResourcesByDepartment].[All]),
    IIF( Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],NULL, [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])
    )
WHEN
[Resources].[ResourcesByDepartment].currentmember is [Resources].[ResourcesByDepartment].[Department]
THEN
  SUM(
    except(VisualTotals(axis(1)), [Resources].[ResourcesByDepartment].[Department]),
    IIF( Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],NULL, [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])
    )

ELSE
    IIF( Measures.[Timesheet Hours] >= [Measures].[Scheduled Hours],NULL, [Measures].[Timesheet Hours]-[Measures].[Scheduled Hours])
END

Any other suggestions?

Thanks.