Remove blanks from the lowest level in a Parent-Child hierarchy

[Anonymous]

Hi,

I have a fact table for time registration called hours, where all registrations are placed in time buckets.

I filter this table from a time buckets dimension table and have a key to do so called "bucket key".

The trouble I'm facing is when the users drill down to time bucket level 3 in a bar chat, they see a blank bar with some hours in it. This is because not all time registration needs a value in level 3, so technically Power BI is showing the correct sum:

The blank bar on time bucket level 3

It there a way to make the blank bar disappear on level 3, while still keeping correct the sums on level 2 and 1?

If you apply a simple filter on level 3 to remove the blanks, the hours will also go missing on level 2 and 1.

I have been looking into the Parent-Child Hierarchies, but I'm not sure this is applicable in my case?

Sample M code for the fact table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY8xC8IwEIX/SsjcQhPFXXRRugk6lA4xfWBoegltLPjvTU0R69Tljsf7PrirKn52D+IZF0UcN9e3cR0xwjrfgUJMe+8/fS5yyetshTHjv7Cc4BMF9KQsC6ZDzAfXeQSQBmswJlF+xWfzink3XXAfQMNkXIxumYUakejNH70tFng5k1elVTCOkiGXjpDrPq/f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person = _t, Hours = _t, #"Bucket 1" = _t, #"Bucket 2" = _t, #"Bucket 3" = _t, #"Bucket key" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person", type text}, {"Hours", Int64.Type}, {"Bucket 1", type text}, {"Bucket 2", type text}, {"Bucket 3", type text}, {"Bucket key", type text}})
in
    #"Changed Type"

[wes-verh · Accepted solution]

Please consider voting to my idea to add this option to the Matrix visual:

https://community.powerbi.com/t5/Custom-Visuals-Ideas/Hide-empty-hierarchy-levels-in-Matrix-visual/i...

[v-juanli-msft]

Hi @Anonymous

As tested, when filter out the blank value of time bucket level 3 in visual level filter, it doesn't show the correct sum on level 2 and level 1. 

I only come up with this workaround, add hierarchy slicer on the report, when drill down to the level3, select not blank items.

Best Regards

Maggie

[Anonymous]

That might work but isn't really user-friendly.

I guess that I will have to look into the Parent-Child hierarchies, and see if I can make the sums on each level.

[Anonymous]

If works on my env, you can try this.

Open Power Query Editor, select Bucket 3, choose Replace Values

Then, replace Blank to null.

Save the change and go back to your chart and create a visula level filter to make Bucket 3 is not blank

[Anonymous]

Thanks for the reply, but your solution also filters out the hours on level 2 and 1, so I can't apply it.

[v-juanli-msft]

Hi @Anonymous

I mean when you drill down to the level3, then to make the blank value in X-Axis not shown,unselect blank in the hierarchy slicer.

Best Regards

Maggie