Dynamic Picklist

[dparkar]

Hi Everyone,
My scenario is I have A cube with 3 dimensions
Days, Department & Measure.
In measure i have 3 elements that are picklist by example

Capture.PNG (28.6 KiB) Viewed 3337 times

.
I have a dimension in which there are opportunity 1 2 3 & 4
I need if i have selected a member in opportunity 1 from the picklist that member should not be available for opportunity 2 picklist.


i have tried the mdx filter option which is not working in this case as i dont have the dimension in my cube which is used as picklist.
thanks in advance

[scrumthing]

You could maybe try writing rules and using a lookup cube which counts the use of the picklist values and base your subset for the picklist on that.
But I am not sure that it is going to work properly.

[Mark RMBC]

Hi,

Assuming you literally only want to rule where a person is in opport1 they should not appear in opport2 picklist, then I got this to work as follows:

Create a clone of your existing cube but with the person dimension and a measure dimension added, this measure will hold a 1 or 0.

This cloned cube will have a rule along the following lines:

['opport1','Value']=N:

If(!Person @= DB('1_Test','opport1',!h_days,!m_Measure),1,0);

Create a couple of subsets, one all n level people, the other MDX based to filter for employees whose value results in zero for the rule above,
so something like

{Filter(TM1FILTERBYLEVEL( {TM1SUBSETALL( [Person] )}, 0), [1_TestClone].([Person].currentmember, [h_department].[Opport1], [m_Measure].[All_Shifts],[h_days].[All_Days], [Measure].[Value]) =0)}

Then create a picklist cube on the 1_test cube with the rule something like:

['opport1']=S:

'Subset:Person:N_Level_Person';


['opport2']=S:

'Subset:Person:Emps Not in Opp1';

Hopefully that can give you an idea where to start!

regards,

Mark

[dparkar]

Thanks Mark it worked