How to remove blanks and repeats form tables and charts in parent child hierarchies #powerbi #dax

Should core PowerBI visuals natively support Parent Child hierarchies? Yes... do they? No... We have to deal with blanks and repeats and all kinds of things to make our charts and tables look nice. Well, in this video I offer you my approach to clean up your visualizations so they look exactly the way you want them to look.

this is the DAX code that i used in this video, first we need to create a measure that allows us to understand what level of hierarchy we currently are in.

Current Level = 
ISFILTERED(Org[Level 1]) + 
ISFILTERED(Org[Level 2]) +
ISFILTERED(Org[Level 3]) +
ISFILTERED(Org[Level 4]) + 
ISFILTERED(Org[Level 5]) + 
ISFILTERED(Org[Level 6]) 

Then we write another measure that will allow us to say if we want to keep the current hierarchy member in the visual or not.

mustHide = 
SWITCH
(
    [Current Level],
    1, FALSE(),
    2, ISBLANK(SELECTEDVALUE(Org[Level 2])),
    3, ISBLANK(SELECTEDVALUE(Org[Level 3])),
    4, ISBLANK(SELECTEDVALUE(Org[Level 4])),
    5, ISBLANK(SELECTEDVALUE(Org[Level 5])),
    6, (SELECTEDVALUE(Org[Level 5]) = SELECTEDVALUE(Org[Name/Role])) || ISBLANK(SELECTEDVALUE(Org[Level 5]))
)

And lastly, we take our business measure that we would like to display in our table, matrix or other chart.

Headcount = 
IF
(
    [mustHide], 
    BLANK(), 
    IF
    (
        ISBLANK([Direct Reports]),
        1,
    
        var _path = [Selected Leader]

        RETURN
        CALCULATE
        (
            COUNTROWS(Org), 
            FILTER
            (
                ALL(Org),
                PATHCONTAINS(Org[path], _path) && _path <> Org[ID]
            )
        )
    )
)

and now you are cooking with gas :)