Hierarchy selection

[PBIfanatic]

Hi, 

My data structure looks like this, SKU3 will contain  all 3 levels. 

SKU1(Parent)	SKU2(Child1)	SKU3(Child2)
HTC1	HTC1	HTC1
Apple1	Apple1	Apple1
Apple1	Apple1	Apple4
Apple1	Apple1	Apple5
Samsung1	Samsung2	Samsung3

Creating a hierarchy and placing in the slicer gives this

Can this be showed where the value doesnt repeat itself?

thanks 

[TheoC · Accepted solution]

Hi @PBIfanatic, it can be done a few ways, one of which is by creating a calculated column and just using an IF statement. For exmaple, the below two calculated columns will get you what you want:

Child_1 = IF ( PBI_Table2[SKU2(Child1)] = PBI_Table2[SKU1(Parent)] , "" , PBI_Table2[SKU2(Child1)] )

Child_2 = IF ( PBI_Table2[SKU3(Child2)] = PBI_Table2[SKU1(Parent)] , "" , PBI_Table2[SKU3(Child2)] )

To achieve your example, I didn't need to bring in Child_1, however, if you are to add many more rows, it is likely you will need to bring them in.

Also, just as an FYI, if you have three specific columns such as that which you have presented as an example (i.e. Parent, Child1, Child2), you could use Power Query to Unpivot your tables and create the following very easily (however, it may not be what you want?).

Anyway, hope one or the other help 🙂

Theo

[PBIfanatic]

Thanks @TheoC  for the explanation. 

Thats very helpful. 

Its closer to what I want. There are blanks at each level where the parent is the child. Is it possible to get rid of the blanks in the slicer? that would be best if possible. 

I have seen a hierarchy slicer visual which can control the blanks but its not performant. 

[TheoC]

@PBIfanatic, there are a few ways to achieve this:

1. I believe the best way is through the use of Power Query being the second example I provided. 

2. You can untick "blanks" from the Filter pane of the visual when you click on "Child_2" field.

3. Finally, when you add "values" to the Matrix visual, you can use the Filter pane on the visual, click on the values, and then use "Basic Filtering" to untick 'blank'.

Best of luck! 🙂

[PBIfanatic]

thanks @TheoC