Formatting Rule Grouping

[Ben_Rix]

Hi,

I have about 6 formatting rules that are being applied to result in an icon appearing on a column in the primary view. If more than one rule is true it produces more than one icon. Now I only want one icon to appear in the main view, but, when i go into the detail view, then I want to see all the individual rules that are true.

I was thinking the only way to do it would be to do a very long winded, if and that formula, but, would this cause performance issues, or, does anyone have a nifty method that would accomplish this task.

Thanks in advance. Ben

[Steve]

Virtual column?

[Ben_Rix]

Do you mean create a virtual column for the IF statement and then let the formatting rule decide on that. Sorry to be a pain, it got a bit late here and I’ve been staring at the screen for a while so it may make more sense then.

[Steve · Accepted solution]

My thought is to have a single virtual column of type EnumList with an App formula expression like:

(
  LIST(
    IFS(test-condition-1, "tag1"),
    IFS(test-condition-2, "tag2"),
    ...,
    IFS(test-condition-N, "tagN")
  )
  - LIST()
)

where test-condition-1 is the expression you would have used for the first of the “about 6 formatting rules” you mentioned, tag1 is a distinct value whose presence indicates test-condition-1 succeeded. And so on for -2 through -N. - LIST() removes blank list entries corresponding to test conditions that fail, ensuring the list only includes the tags of successful tests.

Next, the format rule conditions would be like:

IN(
  "tag",
  TOP(
    [format-tags],
    IF(
      ("Detail" = CONTEXT("ViewType")),
      999,
      1
    )
  )
)

where tag is the tag whose presence in the list in the virtual column from above indicates the format rule should be applied; and format-tags is the name of the virtual column from above.

In a detail view, this expression looks to see if the format rule’s tag occurs at all in the list of format tags; outside a detail view, the expression looks to see if the format rule’s tag is the first in the list of format tags.

Order the tests and tags in the virtual column list such that those with higher priorities come before those with lower priorities.

Note that using a virtual column might affect sync times.

---

For example:

(
  LIST(
    IFS(([Cost] > 14.0), "High Cost"),
    IFS(IN([Color], {"Magenta", "Cyan"}), "Odd Color Choice"),
    IFS(IN([Email], Admin Users[Email]), "Admin User"
  )
  - LIST()
)

IN(
  "Odd Color Choice",
  TOP(
    [Format Tags],
    IF(
      ("Detail" = CONTEXT("ViewType")),
      999,
      1
    )
  )
)

[Ben_Rix]

Hey Steve Thanks a lot for that detailed response