I have a calculated measure as follows called Effective Tax Rate.

([Tax Paid] + [Use tax Accrued]) / [Merchandise Amt]

I need to add filtering to the denominator. The pseudocode would look like
this.

([Tax Paid] + [Use tax Accrued]) /
[Merchandise Amt where State (dimension) <> '']

Could someone help me out with the MDX syntax for this?

I didn't fully understand your questions, but, I think you mean to use a
construct like this:

IIF (<dimension>.CurrentMember <> 'something' AND [Merchandise Amt] <> 0,
([Tax Paid] + [Use tax Accrued]) / [Merchandise Amt], NULL)

Murthy

"ChrisBusch" wrote:

Close, but getting a syntax error. The test is State = ' '

I get invalid token on this...

IIF (State.CurrentMember = ' ' AND [Merchandise Amt] <> 0,
([Tax Paid] + [Use tax Accrued]) / [Merchandise Amt], NULL)



"MurthyJ" wrote:

You can try one of these forms to see which one fits what you need:

State.CurrentMember.UniqueName= "" (notice double quotes, not single quotes)
State.CurrentMember IS NULL

Murthy

"CBUSCH" wrote:

Got the syntax working, but the test on state is not working.

I tried...

IIF (State.CurrentMember.UniqueName= " ",[Merchandise Amt], NULL)
and...
IIF (State.CurrentMember.UniqueName= "",[Merchandise Amt], NULL)
and...
IIF (State.CurrentMember IS NULL,[Merchandise Amt], NULL)

They all return Merchandise Amt regardless of what value is in State.
I'm not sure what the value in State is blank, empty string, or nulll
The value in the source database is blank (" ")

Thanks for your help with this.





"MurthyJ" wrote:

For uniquename test, you would have to use the preceding level values also
(for instance, [State].[All State].[ ], depending on whatever other levels
you may have). Sorry I might have misled you in my earlier sample.

Use Name instead of UniqueName as in

IIF (State.CurrentMember.Name= " ",[Merchandise Amt], NULL)

Murthy

"CBUSCH" wrote: