Use Formula summary on a line item using Users list

Emmeline
Emmeline
in Modeling

Hi,

I want to create a user filter to display on a board. Let's suppose this filter applies to a "Department" list, which belongs to a hierarchy.

My goal is: when no filter is selected, display:

  • All departments if the card is a grid / column chart (where departments are the column) → ok
  • Total organization (ie top level of Department hierarchy) if the card has Department as a page selector dimension → ko

For this second bullet point, I have already managed to do this using a boolean formula as following: ISBLANK(filter selector) and is_top_level or filter selector = department item, and then apply "Formula" as summary method.
The is_top_level is a calculated line item, using some ratio summaries to identify the top level.

But today, I tried to apply this "Formula" summary method on the boolean line item, and received an error saying:

Filter department does not apply to O3 Department
(Filter department applies to O3 Department, Users)

I am pretty sure I've used this method before, so can anyone help me please?

Best Answer

  • M.Kierepka
    M.Kierepka
    Answer ✓

    Hi @Emmeline ,

    Just to ensure, your hierarchy is multi-list? Then remember you cannot use ITEM() formula with summary formula - for upper levels it would result in error. My solution:

    obraz.png
    obraz.png

    Pasting below simple solution for single-list hierarchies - for reference.

    obraz.png
    obraz.png

Answers

  • Emmeline
    Emmeline

    Hi @M.Kierepka

    Thanks for your answer. I didn't realize I was using "item()" function in my formula. I replaced it by a line item who stores the item() function result.

    Thanks a lot!

Categories