Forum Discussion

PBIfanatic's avatar
PBIfanatic
Helper V
4 years ago
Solved

Hierarchy selection

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 ...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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

     

TheoC's avatar
TheoC
Community Champion
4 years ago

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! 🙂