Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
timtack
Frequent Visitor

Static conditional data color formatting all visuals with same hierarchy

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

 

Level1Level2Level3Level4ProductIDColor Hex Code
Bike    #0B580008
Bikefront parts   #0B5B0008
Bikefront partssteering  #0B5E0008
Bikefront partssteeringsteering wheelst_03#0B610008
Bikefront partssteeringstemSt_04#0B640008
Bikedriveswitching gear sg_07#0B670008
Bikedrivechain ch_11#0B6A0008
Bikedriveshifter sh_15#0B6D0008
Bikedrivewheels wh_123#0B700008
BikePackage  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.

 

1 REPLY 1
Chris_White
Resolver II
Resolver II

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.



Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.