Conditionally formatting hierarchy levels with "Personalise this Visual"

[Anonymous]

Hi All,

Wanting to create a matrix with it's values conditionally formatted based on hierarchy level. I also want end users to be able to "Personalise this visual" from the service and change fields in the hierarchy.

Currently using ISINSCOPE to format based on hierachy. However, if a user was to change the highest level from Portfolio to Strategic Theme, the conditional formats no longer apply.

Trying to determine if there is an alternate way to conditionally format:

VAR L1 = ISINSCOPE(Projects[Portfolio])

VAR L2 = ISINSCOPE(Projects[Program])

VAR L3 = ISINSCOPE(Projects[ProjectName])

RETURN

IF([Gantt Dates]<>0,

SWITCH(

TRUE(),

L3, "#638EB090", L2, "#638EB0", L1, "#1F4C71")

))

[amitchandak]

@Anonymous , refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Anonymous]

Thanks,

I was trying to determine if there is a way to reference hierarchy level without "hard-coding" the hierarchy field name. I couldn't see how that could be done using ISINSCOPE.

[V-lianl-msft]

Hi @Anonymous ,

Try using functions such as path to get the highest level hierarchy, and then use the isinscope function according to your previous logic.

https://docs.microsoft.com/en-us/dax/pathlength-function-dax 

https://docs.microsoft.com/en-us/dax/path-function-dax 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It seems you can use PATH, PATHLENGTH and PATHITEM when you have two known columns in a table. How would you utilise these in a matrix visual with unknown row headers?