Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Ragged Hierarchy?

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 ...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    Hi Anonymous , 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!

Royel's avatar
Royel
Icon for Super User rankSuper User
1 year ago

Hi, Anonymous  

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!