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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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