Static conditional data color formatting all visuals with same hierarchy

[timtack]

Hello Community,

i have several charts with a hierarchy as Axis. I want uniform data colors. So if i filter my hierarchy with a slicer and have the same level drilled down in every chart (e.g. lvl3), i want that all visuals have the same color. Therefore i added a new colomn for the hex code

Level1	Level2	Level3	Level4	ProductID	Color Hex Code
Bike					#0B580008
Bike	front parts				#0B5B0008
Bike	front parts	steering			#0B5E0008
Bike	front parts	steering	steering wheel	st_03	#0B610008
Bike	front parts	steering	stem	St_04	#0B640008
Bike	drive	switching gear		sg_07	#0B670008
Bike	drive	chain		ch_11	#0B6A0008
Bike	drive	shifter		sh_15	#0B6D0008
Bike	drive	wheels		wh_123	#0B700008
Bike	Package			pg_55	#0B730008
Bike					#0B760008
Bike					#0B790008
Bike					#0B7C0008

As is mentioned, i want that every level has the same color over all visuals, when they are drilled down into the same level. How can i achieve that? My slicer is selecting multiple lower levels, when i select an item e.g. on level 3 it also selects level4 and ProductID.

I tried it with a second table (colorcode) with only the ProductID and a color hex code, but this only made a uniform color code for the last hierarchy level -> ProductID. I made it this way:

get selected item = 
SWITCH(
    TRUE(),
    ISFILTERED('hierarchy'[ProductID]), SELECTEDVALUE('hierarchy[ProductID]'),
    ISFILTERED('hierarchy'[Level4]), SELECTEDVALUE('hierarchy'[Level4]),
    ISFILTERED('hierarchy'[Level3]), SELECTEDVALUE('hierarchy'[Level3]),
    ISFILTERED('hierarchy'[Level2]), SELECTEDVALUE('hierarchy'[Level2]),
    ISFILTERED('hierarchy'[Level1]), SELECTEDVALUE('hierarchy'[Level1]),
    "select an item"
)

and then a measure as conditional formatting for the data colors:

data color v4 = 
VAR itemm = [get selected item]
Return
LOOKUPVALUE(colorcode[color], colorcode[ProductID], itemm)

This logic is aplied to ProductID, but i don't know how to transform it for all hierachy items.

[Chris_White]

Add another column next to your hex code which is:
[Level1] & [Level2] & [Level3] & [Level4] & [ProductID]
i.e. they're concatenated together

Change your switch statement so it returns [Level1] & [Level2] if you're at [Level2] or [Level1] & [Level2] & [Level3] if you're at [Level3] etc.  I prefer ISINSCOPE for this:

get selected item =
SWITCH(
TRUE(),
ISINSCOPE('hierarchy'[ProductID]), SELECTEDVALUE('hierarchy'[Level1]) & SELECTEDVALUE('hierarchy'[Level2]) & SELECTEDVALUE('hierarchy'[Level3]) & SELECTEDVALUE('hierarchy'[Level4]) & SELECTEDVALUE('hierarchy[ProductID]'),
ISINSCOPE('hierarchy'[Level4]), SELECTEDVALUE('hierarchy'[Level1]) & SELECTEDVALUE('hierarchy'[Level2]) & SELECTEDVALUE('hierarchy'[Level3]) & SELECTEDVALUE('hierarchy'[Level4]),
ISINSCOPE('hierarchy'[Level3]), SELECTEDVALUE('hierarchy'[Level1]) & SELECTEDVALUE('hierarchy'[Level2]) & SELECTEDVALUE('hierarchy'[Level3]),
ISINSCOPE('hierarchy'[Level2]), SELECTEDVALUE('hierarchy'[Level1]) & SELECTEDVALUE('hierarchy'[Level2]),
ISINSCOPE('hierarchy'[Level1]), SELECTEDVALUE('hierarchy'[Level1]),
"select an item"
)

Change [data color v4] so it looks up to the new column you added to return the colour for that level.