Dynamically filter a MemberSet using a Property

[Former Member]

I need to filter a MemberSet in an Expansion based on the CV selection. The property value cannot be hard coded, since it needs to flex as a different item is selected in the CV.

Here is the desired result

ARR_ITEM = "Y" AND ARR_PAGE = "???"

where ??? is equal to the property of the CV selection

[Former Member · Accepted solution]

Hi Debbie,

To accomplish that you can use a combination of excel and bpc formulas. I would do something like this:

in cell AA80, i'll put = "ARR_ITEM =" & AA81

in cell AA81 =EVPRO("application u are using";EVCVW("application u are using";"nameof your dimension");property)

and finally in my member set i'll put =AA80

hope it helps

[Former Member · Accepted solution]

Thanks for the quick reply!

I get an EVDRE error when the member set is set equal to the cell that contains the desired string.

#ERR:Invalid member of flag in <member set>, item: =C11, col# 2

Is there some type of formatting that is required when trying to use a cell reference in the member set?

[Former Member · Accepted solution]

hi,

Thats because of the format of the cell of the member set it's not the correct format, for changing it , you can copy the cell where you made the calculations (or a cell that shows you the result of calculation instead of the formula) and paste it over the member set cell, then put the formula as before and now the will recognice it as a formula and it will put the result intead of the formula,

hope it helps

[Former Member · Accepted solution]

Thanks! Correcting the cell formatting solved the issue.