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
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
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.