Conditional formatting by hierarchy, from top level parent downwards

I have a table report which shows a list of Initiatives at the top level and as you drill down, you can see Epics, then Stories. There are many Initiatives in the table. What I would like to do is set conditional formatting based on the Initiative and everything under it, for the purpose of easier distinction.

So Initiative #1 and all of it’s children may be formatted in blue. Initiative #2 and all of it’s children may be formatted in green, etc. Is this possible in the tool and if so, how do I achieve that?

A less optimal solution would be to highlight every Initiative so that the reader could more easily distinguish the break points.


Hi @drewwiseman

You can use a custom formula in your Conditional cell formatting that for each row finds the Ancestor at your specified level and compares the name of it to the hard-coded Exact values. This will not be very dynamic as you have to hard-code the names/keys of each Initiative.
In these examples, I’m using the Epic hierarchy with the Project level ancestor. Change this to the hierarchy you’re using and use the Initiative level ancestor.

Ancestor([Issue.Epic].CurrentHierarchyMember, [Issue.Epic].[Project]).Name

The “less optimal” solution of highlighting every Initiative would actually be a more dynamic solution as it does not require hard-coded values for Initiatives. (again, I’m using the Epic hierarchy with the Project level. Adjust this for your hierarchy with the Initiative level)


Let me know if this fits your use case or if you have any additional questions on this!
Best regards,
Nauris / eazyBI support

Hey Nauris,

Thanks so much for the response and clear info! The first option is too manual, but the 2nd option is very helpful. That was a good learning for me.

Much appreciated!