Filter category in another matrix

5.09K viewsFormulas and Functions


I am new user try to figure out how can I filter my category dimension for cetain values. In the attached model I have several dimensions out of which one is account. I import actual figures into my matrix through Datalink.
I understand that I can only filter to one specific account in the filter pane therefore I want to create another matrix (operational accounts) which only retrieves the operational accounts (6xxxx accounts) and their values in the appropriate dimensions (project, department) of course.
I have already linked this second matrix to my original one, but not sure how can I apply a filter for the accounts to bring all accounts which are less than 70000. (7xxxx accounts are non-operational ones).
I would make furhter calculations/view on this second matrix.
Please note that the accounts in my 2012 actuals matrix could change from month to month (new accounts could be added) as I import the data monthly,
so I would need a dynamic filter for all accounts less than 70000.

Could anyone advise please?



Here’s a idea. Unfortunately, I don’t have time to try this on your model at the moment.

Create a table view on the matrix and then create a filter to only include accounts starting with 6.

Also, is there a way you can simplfy your matrix? The “amount” item seems redundant.


You would have to use two different formulas as it appears some of the 6xxxx accounts are grouped in FCST accounts and some aren’t. You’d need one for FCST and one for the non-FCST but the following two formulas should work. They’re just recognizing the first digit of the account and acting accordingly. There are probably other ways to do it as well. FCST accounts, account=if(value(left(@level(account, 3), 1))< 7, ‘2012 Actuals (import)’::FCST accounts, “”) skip Sum of operational expense,Sum of project,Sum of Executive,Sum of Copr_Fin,Sum of department,Sum of Y 2012

2.account=if(value(left(@level(account, 2), 1))< 7, ‘2012 Actuals (import)’::account, “”) skip FCST accounts, Sum of operational expense,Sum of project,Sum of Executive,Sum of Copr_Fin,Sum of department,’Sum of non-ops expense’,Sum of account,Sum of Y 2012


Thanks Lyledp and Bryan.

I am not sure if I missed something, but when I enter the formulas Bryan given, it still seems that the 7xxxx accounts are shown and I also get a #VALUE!
error. See attached.


How can I filter for my account dimension/category label (and not for value) in the table view? If I select accounts and and choose filter, I can filter for cell values and for expression, but not for the account values itself.

thanks again for your advice,


Hi Mark,

In your formula 9, you need to have @level(account, 3),1) instead of @level(account, 2),1). There’s an extra level/group in the FCST accounts so the account number is on the third level. You also need a skip FCST accounts in the formula 10 so that the eclipse goes away.

I’m not sure exactly what you want for the filter but you can drag the account category to top of table to only pick an individual account. That may or may not help.



Thanks Bryan. Now it is working. I want to use this second matrix (including values of 6xxxx accounts only) for further analysis.

Latest Questions