Hi I am trying to build what I thought would be a simple calculated
member with no success.

I have a 1 level dimension called Funding which is an integer between 1
and 12.
What I want is for the use to see a dimension that lists 'Domestic'
when Funding = 1, 'International' when Funding = 2, and 'FCR' when
Funding > 2.

To do this I used the following formula:
Iif([Funding].CurrentMember=1,'Domestic',
Iif([Funding].CurrentMember=1,'International',FCR))

However when I browse my cube I see and error symbol and I get the
message:
Infinite recursion detected during execution of calculated member
FundingType

Can someone point me in the right direction please.

Also is this the right way of going about this kind of problem or
should I really be making sure the summarised dimension is in my data
table I import from my data source?

Thanks
Mark

You are better off changing the dimension table so that it has a column
with the Name.

eg.

1, Domestic
2, International
3, FCR
4, FCR
5, FCR
....

If you use the name as both the key and the caption column, the repeated
FCR entries will be rolled up into one member.

If the numbers between 3-12 mean some specific you could create a ragged
hierarchy like the following

1, Domestic, Domestic
2, International, International
3, FCR, FCR 1
4, FCR, FCR 2
5, FCR, FCR 3

You can then set up the 2nd level to hide members where the name is the
same as the parent so the dimension will appear as below.

Funding
- Domestic (no children)
- International (no children)
- FCR
-- FCR 1
-- FCR 2
-- FCR 3

Although you can put text in calculated measures it is seldom useful for
much else than debugging. Many client tools assume that the measures
will come back as numeric values and will ignore text.

--
Regards
Darren Gosbell
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1126221660.936991.244160 (AT) g47g2000cwa (DOT) googlegroups.com>,
m.s.easton (AT) gmail (DOT) com says...

Thanks for your reply.
How about when I want a dimension such as age range for a group of
people.
For instance if my measure is a userid and I have a dimension as
birthdate and I want to report on the distinct count of userid for a
calculated dimension of age range
<18,18-20,20-30,30-40,40+ etc

This is the same situation as the first question I asked except it is
not feasible to have a table with each birthdate linked to an age
range.

I have complete control of my source tables so I could calculate the
age range category when I built my data source. Would this be the best
solution?

Cheers

Yes, calculating the ranges on import is the best way to go. What I have
done in the past is to set up a couple of levels 10yr groups, 5yr groups
going down to the single years.

Some users hesitate at locking in on specified ranges, they do lose a
little flexibility, but at the gain of enforced consistency. I have seen
issues where one user would group 21-25, 25-30... and the next would do
20-24,25-29... With this logic encoded into the dimension table you are
guaranteed of consistency across the organisation. And if you have a
hierarchy so that the single year members are still in there are the
leaf level it is still possible to extract ranges like 18-21 if you need
to.

--
Regards
Darren Gosbell
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1126230965.281113.321850 (AT) f14g2000cwb (DOT) googlegroups.com>,
m.s.easton (AT) gmail (DOT) com says...