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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors