How to eliminate 'blank' bars in a chart using drilldown and variable hierarchy path length

[justlearning50]

Hi I'm using 2 tables - one with data, and one with the hierarchy (teams). When I drilldown I always have a 'Blank' column. I know I can filter out the blanks using the filter panel, but that removes a team from the previous level where that was the lowest level in that particular hierarchy.

eg below with blanks selected in the Level 3 filter

At level 2 I get all 4 groups

 

 

And at Level 3 I have a 'blank' column which I don't want.

 

If I filter out the blanks in the Level 3 filter, I get this at level 2 - first group is missing as it was the lowest level in its hierarchy path.

I understand why this is happening, just not sure what to do about it. I've watched a number of videos, but I'm not sure how to use the information in this case (generally ISINSCOPE), as I'm using two tables, and they generally are looking at a matrix and using one table. 

I hope this makes sense. Thanks in advance for your help.

[rajendraongole1 · Accepted solution]

Hi @justlearning50 - we'll use ISINSCOPE to determine which level of the hierarchy we are currently in and handle 'Blanks'

I have taken example with sales amount replace with your table and fields as per your reference.

Create a below DAX measure:

create displaysales to avoid blanks.

DisplaySales =
IF(
ISBLANK(SUM(DataTable[Sales])),
BLANK(),
SUM(DataTable[Sales])
)

use the above Displaysales measure in your conditional measure with hierarchy .

ConditionalDisplay =
IF(
ISINSCOPE(HierarchyTable[Level3]),
[DisplaySales], // Only show non-blank sales at Level 3
IF(
ISINSCOPE(HierarchyTable[Level2]),
IF(
NOT(ISBLANK([DisplaySales])),
[DisplaySales],
BLANK()
),
IF(
ISINSCOPE(HierarchyTable[Level1]),
[SalesAmount],
BLANK()
)
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[justlearning50 · Accepted solution]

Hi @rajendraongole1 , this didn't quite work for me, maybe because I had more levels than I showed) but it helped me on the way, so thank you.

What did work in the end was this

measure 1

HideCompliance = IF

    (

         [LevelScope] <= min(table_name[Path Length]) ,1

    )

// Pathlength is a calculated column : Path Length = PATHLENGTH(table_name[Hierarchy path])

I used a SWITCH() function to assign mumbers to my levels

LevelScope =

SWITCH (

    TRUE (),

    ISINSCOPE ( table_name[Level6] ), 6,

    ISINSCOPE ( table_name[Level5] ), 5,

    ISINSCOPE ( table_name[Level4] ), 4,

    ISINSCOPE (table_name[Level3]), 3,

    ISINSCOPE ( table_name[Level2] ), 2,

    ISINSCOPE ( table_name[Top Level]), 1

)

Then I just used the variable * [HideVisual] and plotted that. 

Averagecompliance = CALCULATE

    (

        AVERAGE

            (

                Table column

            )

            * [HideCompliance]

    )

It may not be perfect DAX, but it worked from what I can see. 

Hope this helps someone else.

[rajendraongole1 · Accepted solution]

Hi @justlearning50 - we'll use ISINSCOPE to determine which level of the hierarchy we are currently in and handle 'Blanks'

I have taken example with sales amount replace with your table and fields as per your reference.

Create a below DAX measure:

create displaysales to avoid blanks.

DisplaySales =
IF(
ISBLANK(SUM(DataTable[Sales])),
BLANK(),
SUM(DataTable[Sales])
)

use the above Displaysales measure in your conditional measure with hierarchy .

ConditionalDisplay =
IF(
ISINSCOPE(HierarchyTable[Level3]),
[DisplaySales], // Only show non-blank sales at Level 3
IF(
ISINSCOPE(HierarchyTable[Level2]),
IF(
NOT(ISBLANK([DisplaySales])),
[DisplaySales],
BLANK()
),
IF(
ISINSCOPE(HierarchyTable[Level1]),
[SalesAmount],
BLANK()
)
)
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!