cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sascha_17 Frequent Visitor
Frequent Visitor

Flattened Hierarchy - How to hidden blanks

Dear all

 

I have a hierarchy, which is flattened with different countrys - Please see picture below:

 

 

When I create now a hierarchy in the PowerBI Desktop and add this in the hierarchy slicer, I always have a 'blank' value on level 3 for Europe, as there is no name defined. Is there an option or workaround to hide this ? I know in SSAS there is the option 'Hide Member if' to hide the next level when its the same value...

 

Thanks a lot for an answer and your help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Flattened Hierarchy - How to hidden blanks

Hi @Sascha_17,

Do you use HierarchySlicer? If that is the case, the feature that hide blanks is in development and the feature will be released in the next version(0.8.x), for more details, please review this blog: https://azurebi.jppp.org/2016/04/02/changelog-hierarchy-slicer/

In addition, to exclude the blanks in visual, you can use "Visual Level Filters" or "Page Level Filters" or "Report Level Filters". Drag the field that you want to filter into one of those areas, and only select the options you want, or do an advanced filter and exclude the blank.
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: Flattened Hierarchy - How to hidden blanks

Hi @Sascha_17,

Do you use HierarchySlicer? If that is the case, the feature that hide blanks is in development and the feature will be released in the next version(0.8.x), for more details, please review this blog: https://azurebi.jppp.org/2016/04/02/changelog-hierarchy-slicer/

In addition, to exclude the blanks in visual, you can use "Visual Level Filters" or "Page Level Filters" or "Report Level Filters". Drag the field that you want to filter into one of those areas, and only select the options you want, or do an advanced filter and exclude the blank.
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Flattened Hierarchy - How to hidden blanks

In the meantime,  there is an excellent article that you can read here that will solve the problem. 

 

http://www.daxpatterns.com/parent-child-hierarchies/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Honey Member
Member

Re: Flattened Hierarchy - How to hidden blanks

H all,

I am facing the similar issue where i can see blank records in hierarchy when created using matrix visual.

If i use visual filter, it completely eradicates all levels containing blank in that level.

 

Please help me find a workaround to this problem?

 

How to remove blank from hierarchy without disrupting structure of hierarchy.

EricGottlieb Frequent Visitor
Frequent Visitor

Re: Flattened Hierarchy - How to hidden blanks

 

Here is the solution!

 

Hi all,

 

I was having the same problem and I could not find anything on the web that truely solved this issue, so I developed my own modeling that would address this problem of "blanck"s appearing in visualization from ragged hierarchies.

 

Here is my solution:

 

On a high level, the solution calls for your hierarchy data table to be segmented into separate data tables to then be interconnected with a 'many to one' relationship.  These hierarchy lookup tables then will be connected to your primary data table.

 

Take the following two examples I created to validate this method

 

Solution 1 (does not work):

 

1. Create a Hierarchy table that you will use as a lookup for your primary data.  the format of this table is shown below. 

      a. Create a primary key column that refers to your last level

      b. Create several columns that describe your hierarchy i.e. L1, L2, L3, ect.

      c. Do not repeat members or level blanks before the end of your hierarchy

2. Create a many to one relationship between your hierarchy lookup table and our primary table.

3. Run a matrix visualization b dragging the values column from your primary data table into the values field and drag all your levels (L1, L2, L2 ect.) into the row section of the visualization.

 

You will notice the "blank" values problem persists.  To resolve this, try solution 2 shown in the next set of pictures

 

 

Capture22.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Solution 2:

 

1. Segment the larger hierarchy data lookup table into 4 separate data tables. Follow the picture examples below for exact designing

      a. As you move from one hierarchy table to the next, generate new levels and tables and eliminate those observations in subsequent tables once the final levels of that observation has been reached.  Please see picture below for further detail.

2. Link your tables together following the specific relationship cardinality as shown below

3. Run a matrix visualization by dragging the values column from your primary data table into the values field and drag your different levels (L1, L2, L3 ect.) from each new hierarchy data tables into the row section of the visualization.

 

Following these steps will resolve the blank values you see when displaying hierarchical data that is builded off of ragged hierarchies.

 

Capture33.PNGCapture444.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I hope this helps you and anyone struggling with this issue.

 

Best,

Eric

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 48 members 1,186 guests
Please welcome our newest community members: