Displaying only child values in parent-child - Unplugged #46

[@danyo101]

Wow. I was trying to realize this since two working days. I did all other tutorials and nothing worked. Big thank for you effort.

[@ajmorgan591]

@9:23 the "Total" value for the "No Agg" measure is blank. How to populate (with 220 in this case)?

[@janp3648]

Wow ! Thank you for this video. Very interesting user case and solution. I never hear about PATH before so very nice introduction.

[@kunwang2279]

Great video as always. But seems that DAX could only handle balanced hierarchy, which means one child always belong to one parent like Year-Quater-Month. I am wondering if unbalanced hierarchy like BOM could handled in power bi. for example, Component A (1 PC) is composed of B (2PC) , C(3PC), D(4PC), while B (1PC) again is composed of C(2PC) and E(1PC), then C (1PC) again is composed of D(3PC) and F(1PC) , and all these information is stored in a single table with 3 column, parent, component, qty. Then how could this hierarchy information be shown in power bi with Dax, and how to extend the BOM of component A to only include the lowest level with the quantity D (25 PC), E(2 PC) and F(7 PC).

[@SQLBI]

You can find several examples here: www.daxpatterns.com/parent-child-hierarchies/ The real issue for BOM is that you don't have a real good native solution for the multi-level organization - a pre-processing that allocates B native components into A components is required. It's possible, but relatively complex and not really managed by DAX because there is no recursion available in this language.

[@kunwang2279]

@@SQLBI Thanks for your clarification. Then the best way is still to use SQL common table expression to do recursive join to extend the BOM. hope one day DAX could handle Recursion.

[@KenLoewen]

In your video, you're displaying the problem I'm trying to solve - when I see the children indented below the parent, I have a blank label for the data applying to the parent. How do I get the parent label to populate in the child level when the parent has data?

[@NikitaKobtsov]

Hi Alberto, Is it possible to show always child value ? What I mean is that if we add in your file slicer with hierarchy (Dim[Lev1],Dim[Lev2],Dim[Lev3]) and than select "F" from Dim[Lev3], we will see "No Agg" at all level - 25, but the desire result would be A - 110, B - 40, F - 25

[@tianhsiunghoon6153]

Thanks. The solution is great for for the data i'm working with. I am using a Hierarchy Filter (with RLS) to control the PBI matrix visual. Can I only display the current and child nodes in the matrix visual such that values across all levels are consistent? Thanks! Base Matrix Root | 90 A | 100 A.1 | 30 A.2 | 10 B | 70 B.1 | 30 B.1.2 | 12 Select B.1 on Hierachy Required: B.1 | 30 B.1.2 | 12 Current result: Root | 30 B | 30 B.1 | 30 B.1.2 | 12

[@rogeriopsvalle]

Hi Alberto, Good evening. A co-worker left the company and now I need to edit an APP that he is the owner of. I have to revise a few measures but I am not able to edit them. Options to download, etc are greyed out. Is there any solution to this case? Is there any application that I can use ? Thanks

[@nathanielmoss7470]

Perfect solution for my needs!! Thabk you!!!

[@rokiczaaa]

Today I tried to do something similar to this but I had no idea. I wanted to show only parent value and hide child level. I think it will be a modification of this case. Thanks :)

[@neelshah8247]

Currently facing a challenge which has ragged hierarchies. I have 5 levels of hierarchy in my data i.e. five different columns called L0, L1, L2, L3 and accounts. A single pair of L0, L1, L2 and L3 can have multiple accounts. I want to create a matrix visual in Power BI that shows this hierarchy in the rows of matrix i.e., L0, L1, L2, L3 and accounts. The challenge that I'm facing is that the hierarchy is ragged. For example: the first row contains blanks in L2 and L3 but there are accounts associated with L1, in that case my hierarchy should be L0, L1 and accounts. In second row L3 is blank but L2 has accounts associated with it, in this case the hierarchy will be L0,L1,L2 and accounts. In short, I want to remove the blanks in between the hierarchy. How can I achieve this in Power BI (matrix visual)?

[@abhishekparihar1]

Hey great explanation , Q - what if we have one more level of Parent child relationship, eg Super parent- Parent- Child

[@GauravKapoor7788]

What if 1 employees report to 2 manager?

[@SQLBI]

It is a graph, you should duplicate the node but then you no longer have a 1-to-many relationship.

[@Bharath_PBI]

Thank you 👍

[@SandipanSarkar-c8v]

Finished watching