Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
5 months ago
Solved

How to exclude blanks from decomposition tree?

The data for this chart is in tabular (pivoted) layout. For the Non-manufacturing and Services categories, the subcategories will always be blank. Is there a way to keep the (Blank) category from displaying? I tried filtering out the blanks from subcategory visual-level filter but that ended up hiding the category as well.

 

 

Basically, the output we'd like to get for the other two categories should look like this:

 

Sample data:

 

ID Facility ActivitiesProduce
1 ManufacturingAgricultural Products/Forestry/Plants
2 Non-Manufacturing 
3 ManufacturingManufactured Products
4 ManufacturingAgricultural Products/Forestry/Plants
5 Non-Manufacturing 
6 ManufacturingManufactured Products
7 Services  
8 ManufacturingAgricultural Products/Forestry/Plants
9 ManufacturingManufactured Products
10 ManufacturingAgricultural Products/Forestry/Plants
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    Could you try the measure below?

    Tree Count =
VAR _activity =
    SELECTEDVALUE ( 'Table'[Facility Activities] )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Produce] )
            && _activity <> "Manufacturing",
        BLANK(),
        DISTINCTCOUNT ( 'Table'[ID] )
    )

4 Replies

  • MohdZaid_'s avatar
    MohdZaid_
    Super User
    5 months ago

    Hey olimilo  , 

     

    In a Power BI Decomposition Tree, blank values in the next level (such as Produce for Non-Manufacturing and Services) will automatically appear as a “(Blank)” branch because the column exists in the model. Filtering out blanks at the visual level does not work properly, since it removes the entire category instead of just hiding the blank node. The best solution is to create a new calculated column that only returns the Produce value when the Facility Activity is “Manufacturing” and returns BLANK() for the other categories. Then use this new column in the Decomposition Tree instead of the original Produce field. This prevents the tree from generating a “(Blank)” branch while still keeping Non-Manufacturing and Services visible at their level.

     

    If this explanation helped, please mark it as the solution so others can find it easily.

    If it helped, a quick Kudos is always appreciated it highlights useful answers for the community.

    Thanks for being part of the discussion!

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    5 months ago

    Could you try the measure below?

    Tree Count =
VAR _activity =
    SELECTEDVALUE ( 'Table'[Facility Activities] )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Produce] )
            && _activity <> "Manufacturing",
        BLANK(),
        DISTINCTCOUNT ( 'Table'[ID] )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 months ago

    Hi olimilo,

     

    Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to cengizhanarslan and MohdZaid_  for prompt and helpful responses.

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 months ago

    Hi @olimilo,

     

    Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.

     

    Best regards,
    Prasanna Kumar