MDX Expression : Calculated Member

0
votes

I am trying to create a Calculated Member on a Microsoft Analysis Services Cube.

There is an existing Dimension [Projects][Project Number - Name] which has members along the line of "12345 - Project X". I need to extract the project number only ("12345").

I initially assumed it would be as simple as LEFT(Projects].[Project Number - Name],5), but this yields a null response.

Even if i just repeat the dimension as is [Projects].[Project Number - Name], I get a null.

I've tried [Projects].[Project Number - Name].Value, [Projects].[Project Number - Name].Members and a host of other ideas, but I am (as you can probably see) shooting in the dark.

I am primarily trying to get this working in Tableau (needs an MDX expression, not full query), but have also tried the Calculated Member functionality in MS Excel Power Pivot, which yields exactly the same answer (null) - which suggests that my MDX is questionable.

1
mdxolap-cubepowerpivottableau-api
See if this helps: social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Tab Alleman
thanks this did get me going in the right direction. I now have `LEFT([Projects].[Project Number - Name].CURRENTMEMBER.NAME,5) working, but only if I also include the original [Projects].[Project Number - Name] dimension included elsewhere in the worksheet. If I take the orgional dimension out the calculated member goes to null... ??? – simonberry

1 Answers

0
votes

I would try :

LEFT( [Projects].[Project Number - Name].currentMember.name, 5 )

Otherwise, [Projects].[Project Number - Name] references a member which then is converted to a tuple and then evaluated. So your expression is actually processing the result of this tuple evaluation and not the name of the member itself.