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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
bharukc
Helper I
Helper I

Change last level color of decomposition tree

I have different level of hierarchy for which I am using decomposition tree for visualization. Some of the levels have null values. I used code below to remove the null values from showing in the tree. However, I would like to color code the last level before null value, so that users would know that they have reached the end for that specific path. 

remove blanks = SWITCH(TRUE(),
ISINSCOPE(Sheet1[Level 5]) && ISBLANK(VALUES(Sheet1[Level 5])), BLANK(),
ISINSCOPE(Sheet1[Level 4]) && ISBLANK(VALUES(Sheet1[Level 4])), BLANK(),
ISINSCOPE(Sheet1[Level 3]) && ISBLANK(VALUES(Sheet1[Level 3])), BLANK(),
ISINSCOPE(Sheet1[Level 2]) && ISBLANK(VALUES(Sheet1[Level 2])), BLANK(),
ISINSCOPE(Sheet1[Level 1]) && ISBLANK(VALUES(Sheet1[Level 1])), BLANK(),
[Count])
 
In below picture, I would like it to "D" bar to have different color, as there is no value for it in level 5.
 
Screen Shot 2022-11-01 at 3.51.02 PM.png

 

1 ACCEPTED SOLUTION

I created a measure and used that measue for bar color formatting. 

 

Color try = SWITCH(TRUE(),
ISINSCOPE(Sheet1[Level 5]) && SELECTEDVALUE(Sheet1[Level 5]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 4]) && SELECTEDVALUE(Sheet1[Level 4]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 3]) && SELECTEDVALUE(Sheet1[Level 3]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 2]) && SELECTEDVALUE(Sheet1[Level 2]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 1]) && SELECTEDVALUE(Sheet1[Level 1]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
"#000000")
 
It did the charm. 
Screen Shot 2022-11-02 at 12.48.23 PM.png

View solution in original post

4 REPLIES 4
v-tangjie-msft
Community Support
Community Support

Hi @bharukc ,

 

You can try using the LASTNOTBLAN()  function or the FLITER() function to filter out the last non-null value, display it as a visual such as a card, and then set the color for the last non-null value.

 

You can refer to the following posts that may be helpful to you:

Solved: Decomposition Conditional Formatting - Microsoft Power BI Community

Solved: color formatting for decomposition tree - Microsoft Power BI Community

Solved: Need to get the last non blank value to use it in ... - Microsoft Power BI Community

Solved: Get most recent non-null value? - Microsoft Power BI Community

Solved: DAX Take the last non-null value in column until t... - Microsoft Power BI Community

 

Best Regards,

Neeko Tang

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

Hi @v-tangjie-msft , 

none of the things I tried using all the links brought me the solution. 

Below is the example source data. Is there a way to put my pbix file here?

IDLevel 1Level 2Level 3Level 4Level 5
1ABCD 
2ABC  
3EFGHI
4EFG  
5EFGH 
6AB   
7EF   
8JKLMN
9JKL  
10JK   
11JKLM 
12JKLM 
13JKLM 
14ABCD 
15ABC  
16EFGHI
17EFG  
18EFGH 
19EF   
20JKLMN
21JKL  
22JK   
23JKLM 
24JKLM 
25ABC  
26EFGHI
27EFG  
28EFGH 
29EF   
30JKLMN
31EFGH 
32EF   
33JKLMN

I created a measure and used that measue for bar color formatting. 

 

Color try = SWITCH(TRUE(),
ISINSCOPE(Sheet1[Level 5]) && SELECTEDVALUE(Sheet1[Level 5]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 4]) && SELECTEDVALUE(Sheet1[Level 4]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 3]) && SELECTEDVALUE(Sheet1[Level 3]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 2]) && SELECTEDVALUE(Sheet1[Level 2]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
ISINSCOPE(Sheet1[Level 1]) && SELECTEDVALUE(Sheet1[Level 1]) = SELECTEDVALUE(Sheet1[Level 5 no blank]), "#FF0000",
"#000000")
 
It did the charm. 
Screen Shot 2022-11-02 at 12.48.23 PM.png

Can you confirm if Sheet1[Level 5 no blank] is a new column created by you? if so, what's the formula used to create it?

 

It would be really helpful if you can attach the file

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Kudoed Authors