Ragged Hierarchy?

[goatmonslc]

Hello,

Reaching out for help as I am desperate after researching for a few weeks. I have one table in a hierarchy structure (entity_tbl) - the max level/depth is 2. This links to the Roles table through a relationship. The functionality is there is a dropdown multiselect - you select a level and it should display all of the roles in a table to the right.

There are a few entries in the entity table where there is only one level. When I add the hierarchy to a dropdown it shows a BLANK for level 2 when there is no value. I have done some research and do have an entity table that shows the structure, etc. but I'm not sure how to use the paths/depths/levels to implement a dropdown with no blanks.

I tried using a custom visual from inforiver but when I use it, the dropdown doesn't exactly work as expected. Any help is so appreciated, as I feel I've exhaused my understanding. I not only want to get this working, but I want to learn too!

Is there a way to attach an example pbix here?

[v-hashadapu]

Hi @goatmonslc , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu · Accepted solution]

Hi @goatmonslc , Thank you for reaching out to the Microsoft Fabric Community Forum.

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

[v-hashadapu]

Hi @goatmonslc , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation.

[v-hashadapu]

Hi @goatmonslc , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[Royel]

Hi, @goatmonslc  

You can use this measure to filter out all blanks while expanding the hierarchy 

Hierarchy Measure = 
VAR CurrentLevel1 = SELECTEDVALUE(Entity[Level1])
VAR CurrentLevel2 = SELECTEDVALUE(Entity[Level2])
VAR IsLevel1InScope = ISINSCOPE(Entity[Level1])
VAR IsLevel2InScope = ISINSCOPE(Entity[Level2])
VAR IsLevel2Blank = ISBLANK(CurrentLevel2)

VAR Result = 
SWITCH(
    TRUE(),
    IsLevel2InScope && IsLevel2Blank, BLANK(),
    IsLevel1InScope && NOT(IsLevel2InScope), COUNTROWS(Roles),  
    IsLevel2InScope && NOT(IsLevel2Blank), COUNTROWS(Roles),
    COUNTROWS(Roles)
)

RETURN Result

You can share your pbix file in many ways:

1. Upload it google drive and share viewer access: https://drive.google.com/drive/u/0/ 

2. Free upload: https://www.file.io/ 

Is it helped? ✔ Give a Kudo • Mark as Solution – help others too!

[goatmonslc]

Since I couldn't find a way to attach my PBIX, below are examples of the two tables. They are linked via relationship - Element-Element. I just don't completely understand how to use the entitypath and depth, etc. to implement a solution.

Entity Table

Roles Table