Hi Experts , 

I have working on SSAS Cube -2012 .
My Cube is ready and is working fine .

I am stuck while creating a Calculate Measure (In writing MDX)

My Requirement is - When a person log in , based on this Role , Calculate Measure should be Visible or Invisible.

I need to use UserName and IIf function to achieve this , but not getting correct MDX to achieve it .

Requirement : Check the ROLE of a person who has logged in using UserName function
If ROLE =1 Then [Measures].[SALES_AMT]

If ROLE =2 Then [Measures].[SALES_AMT]

ELSE NULL

Below is the Screen shot of the Table and Data to give IDEA of the Dimension and Fact Used in the cube 

Emp_ROLE_Fact is Fact Table connected to Employee and Role Dimension in Cube .

Please assist in providing MDX for this Calculated Measure 

Hi Rihan8585,

According to your description, you want to limit the accessing of [Measure].[Sales AMT] for Role 3 and 4. Right?

In this scenario, you can deny the measure accessing for Role 3 and 4 under Cell Data tab in Role. Select Enable read permissions.

Apply the expression in Allow reading of cube content :

NOT Measures.CurrentMember IS [Measures].[Sales AMT]

For more information, please see: Grant custom access to cell data (Analysis Services)

Regards,

Hi Simon , 

I do not want to follow the approach you mentioned , This is what I am trying to avoid and working on to get it work other way. 

As that approach has comes out as a big performance issue .

Can you please help me with the MDX ? to find role of a person who has logged in ??

Please assist !!

Hi,

maybe this helps:

http://beyondrelational.com/modules/2/blogs/65/posts/11566/improving-cell-security-performance-in-ssas.aspx

Hi Experts , 

Can any one help in providing MDX to find the Role of a person who has logged in ? and then a IIF condition to show calculated Meaure ?

Or it can be taken in other way , I need to find the Member which exist against other attribute Member and check its Value and put condition ,as this kind of MDX would help me resolving other issue as well.

Please assist !!

• Edited by Rihan8585 2 hours 25 minutes ago edit

Hi, another solution would be to create the calculated measure as Named Calculation in your DSV and instead creating the calculation use a scope statement with the calculation. Then u can use dimension security instead cell security which performs better.