Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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"
Solved! Go to Solution.
Please consider voting to my idea to add this option to the Matrix visual:
Please consider voting to my idea to add this option to the Matrix visual:
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
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.
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
Thanks for the reply, but your solution also filters out the hours on level 2 and 1, so I can't apply it.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |