Using Users Dimensioned Line Item in a Formula With No Users Dimension

edited May 2023 in Modeling


I have L1 and L2 lists, and recently I added a new flat list as A, and A is actually acting like parent of L1 that determines the groups L1 list members belong to. Since I do not want to mess the existing hierarchy and model functionalities, I prefered adding A as a flat list.

In an L2 system module, I have an L2 and native users list dimensioned filter that filters L2 list members and their parents depending on selection of the list A member. Next, I have a summary module dimensioned by L2, and in that summary module, I use the filter line item I created in L2 system module, and it perfectly works and filters all the L2 and L1 list members. However, I want to use KPIs from the summary module, but as far as I know, line item summary settings show values for the whole list regardless of the filter. So, I want to use a new line item with an IF THEN statement that should reflect the line item values depending on selected list A members and will return 0 (zero) if it is not selected. Since summary module does not have users as a dimension it does not work.

Please let me know if you have a solution for this formula or a better solution in general the overall design of including the flat list A. Thanks

Best Answer

  • vanreign
    edited July 2023 Answer ✓

    Hi HarunT,

    Users List is a popular topic on Community because it is a special List. (1) Check out the Users List; It has Role Access of Full and No Access and (2) no Top Level because individual users do not merger into one user Total. So it is a list that troubles many. However, it is actually simple. (1) If it is a module that requires INPUT, Users MUST be part of the entire module (Applies To); otherwise, users could not INPUT (2) If it is a Summary Module without INPUT, Users is not needed on the whole (Applies To) module. Rather, just a filter is needed for the Summary module dimension plus Users. So if Summary is L2 dimensioned, the filter would be one line Item "Applies to: L2, Users" Boolean Check if the System Module is checked. Then on the System module, Show All User needs to be turned Off and in Context on top for this particular User. Then the IF Statement can be off the Filter. If Boolean checked, show value, If not, Zero. Then the Summary should be good.

    I hope this feedback helps :) If not, let me know. I did try to replicate what you're seeing with tweaks. If you need, I can complete that with snapshots but just wanted to post something now in hope it may help with getting past the issue.




  • Hello @HarunT ,

    The problem you are facing is that your are trying to slice the data based on information that is defined at a user level, but how should it be sliced once you no longer have users? Do you really need to have the mapping defined by the users? Cannot it be the same for everyone?

    In order to modify the summary total of the flat list, you should add users to the final module, but I guess that would increase the size significantly?


    Kind regards,

    Bruno Rodriguez

  • HarunT

    Thanks @BrunoRodriguez for the quick response, and I appreciate your explanation. I am aware of the reason that why the formula does not work. However, it works as a filter since filters can take into account the "current user", and I was looking for an answer that returns the results for the current user. I think "currentuser" function is still not on the horizon, and as you said adding users as a dimension inflates the model size, and I do not want to go that way. If the new filter is not needed to be used frequently, then being the same for everyone could be an option.