cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mholsen Advocate II
Advocate II

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

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:

Level 3.PNGThe 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"

 

5 REPLIES 5
Jessica_Seiya Solution Sage
Solution Sage

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

If works on my env, you can try this.

 

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

2018-10-26_16-09-01.png

 

 

Then, replace Blank to null.

2018-10-26_16-09-58.png

 

 

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

2018-10-26_16-11-29.png

 

 

 

Community Support
Community Support

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

Hi @Mholsen

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.

1.png

 

 

Best Regards

Maggie

 

 

Mholsen Advocate II
Advocate II

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

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

Community Support
Community Support

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

Hi @Mholsen

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

Mholsen Advocate II
Advocate II

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

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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors