CODEWITHSUNDEEP
powerbidaxhierarchy
yaxx0r
yaxx0r

Reputation: 149

How to hide blanks in a matrix visualization with hierarchical rows

I've built a table of data following this helpful guide: https://www.daxpatterns.com/parent-child-hierarchies/

I'm following it exactly but I'll still explain things here so you don't have to go through the whole article if you don't want to. I have a table of Names with corresponding keys, and ParentKeys forming hierarchies. I added a column for the path, columns for each level of the path, depth of hierarchy and an IsLeaf column:

Table of data

If I want to make a matrix and include City (from another table), all hierarchies will expand to the maximum length, and blanks are filled in with the "parent's" name:

enter image description here

The DAX Patterns website explains how to get around this. First add these two measures:

BrowseDepth = ISFILTERED (Nodes[Level1]) + ISFILTERED (Nodes[Level2]) + ISFILTERED (Nodes[Level3])

MaxNodeDepth = MAX (Nodes[HierarchyDepth])

And then you can factor that into calculations with this measure:

Sales Amount Simple = 
IF (
    Nodes[BrowseDepth] > Nodes[MaxNodeDepth],
    BLANK (),
    SUM (Transactions[Amount])
)

If this is the only value on a matrix visual, it turns out fine:

enter image description here

But if I add any other values, I get expanded hierarchies and blanks again:

enter image description here

My problem would be solved if I could filter out blank values, but that filters out the entire hierarchy. Do I have to make a measure using the Sales Amount format above for every value I want to include? I'm trying to add things like addresses that can't be aggregated.

Upvotes: 1

Views: 7587

Answers (1)

Artur
Artur

Reputation: 1

Basiacally yes, you have to re do the measure. However you can embed existing into this patern which makes it a little easier.

Upvotes: 0

Related Questions