Is there a way to crate parent Child relationship on the same table and show results in a table?

[amithegde]

Hi,

I have a table like this:

ItemId | ParentId
1 | 0
2 | 1
3 | 1
4 | 2
5 | 3
6 | 2

Parent Id can be ItemId of another row in the same table.

Now I would like to have a slicer on my dashboard and when I select ItemId 1 on a slicer, I should get rows with Item Id 1, 2, 3 on the resulting table. If I select 2 on the slicer, resulting table will show rows with Id, 2, 4, 6.

[Eric_Zhang]

@amithegde

What do you mean show result in a table? It would be much easier to aggrgate the values rather than show the rows. Could you be more specific? Anyway to get your requirement in the original post, you will need to create a calculated table. Check the sample in the attached pbix.

Table = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            Table1,
            "Itemid", Table1[ItemId],
            "pathLen", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        ),
        SELECTCOLUMNS (
            Table1,
            "Itemid2", Table1[ItemId],
            "ParentID2", Table1[ParentId],
            "path2", PATH ( Table1[ItemId], Table1[ParentId] ),
            "pathLen2", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        )
    ),
    PATHCONTAINS ( [path2], [Itemid] )
        && [pathLen2] - [pathLen]
        <= 1
)

[amithegde]

Thanks @Eric_Zhang

apologies for being vague. By table I meant `table` visualization control. The idea is to be able to browse/see children of a selected item in a tabular format.

The answer you posted is very close. I am looking for a slight modification: If `1` selected on the slicer on the sample, table on the right should display all of the rows (Item ID 1 -6) as they are all children of 1. As of now it works for only one level, in the sense if 3 is selected on the slicer, it shows 3 and 5 which is level 1.

Thanks for the help.. 🙂

[Eric_Zhang]

---

@amithegde wrote:

Thanks @Eric_Zhang

apologies for being vague. By table I meant `table` visualization control. The idea is to be able to browse/see children of a selected item in a tabular format.

The answer you posted is very close. I am looking for a slight modification: If `1` selected on the slicer on the sample, table on the right should display all of the rows (Item ID 1 -6) as they are all children of 1. As of now it works for only one level, in the sense if 3 is selected on the slicer, it shows 3 and 5 which is level 1.

Thanks for the help.. 🙂

---

It works for only one level because I've followed the description in your original post. To achieve the new requirement, you can just remove the filter.

Table = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            Table1,
            "Itemid", Table1[ItemId],
            "pathLen", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        ),
        SELECTCOLUMNS (
            Table1,
            "Itemid2", Table1[ItemId],
            "ParentID2", Table1[ParentId],
            "path2", PATH ( Table1[ItemId], Table1[ParentId] ),
            "pathLen2", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        )
    ),
    PATHCONTAINS ( [path2], [Itemid] )
        //&& [pathLen2] - [pathLen]
        //<= 1
)

[amithegde]

---

@Eric_Zhang wrote:

---

@amithegde wrote:

Thanks @Eric_Zhang

apologies for being vague. By table I meant `table` visualization control. The idea is to be able to browse/see children of a selected item in a tabular format.

The answer you posted is very close. I am looking for a slight modification: If `1` selected on the slicer on the sample, table on the right should display all of the rows (Item ID 1 -6) as they are all children of 1. As of now it works for only one level, in the sense if 3 is selected on the slicer, it shows 3 and 5 which is level 1.

Thanks for the help.. 🙂

---

It works for only one level because I've followed the description in your original post. To achieve the new requirement, you can just remove the filter.

Table = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            Table1,
            "Itemid", Table1[ItemId],
            "pathLen", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        ),
        SELECTCOLUMNS (
            Table1,
            "Itemid2", Table1[ItemId],
            "ParentID2", Table1[ParentId],
            "path2", PATH ( Table1[ItemId], Table1[ParentId] ),
            "pathLen2", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        )
    ),
    PATHCONTAINS ( [path2], [Itemid] )
        //&& [pathLen2] - [pathLen]
        //<= 1
)

---

This works perfect, except that if any value other than `null` which is not in the list ItemId is provided for the first row's parent. Is that a limitation of the CrossJoin?

[Eric_Zhang]

---

@amithegde wrote:

---

@Eric_Zhang wrote:

---

@amithegde wrote:

Thanks @Eric_Zhang

apologies for being vague. By table I meant `table` visualization control. The idea is to be able to browse/see children of a selected item in a tabular format.

The answer you posted is very close. I am looking for a slight modification: If `1` selected on the slicer on the sample, table on the right should display all of the rows (Item ID 1 -6) as they are all children of 1. As of now it works for only one level, in the sense if 3 is selected on the slicer, it shows 3 and 5 which is level 1.

Thanks for the help.. 🙂

---

It works for only one level because I've followed the description in your original post. To achieve the new requirement, you can just remove the filter.

Table = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            Table1,
            "Itemid", Table1[ItemId],
            "pathLen", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        ),
        SELECTCOLUMNS (
            Table1,
            "Itemid2", Table1[ItemId],
            "ParentID2", Table1[ParentId],
            "path2", PATH ( Table1[ItemId], Table1[ParentId] ),
            "pathLen2", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        )
    ),
    PATHCONTAINS ( [path2], [Itemid] )
        //&& [pathLen2] - [pathLen]
        //<= 1
)

---

This works perfect, except that if any value other than `null` which is not in the list ItemId is provided for the first row's parent. Is that a limitation of the CrossJoin?

---

It is actually a limitation of PATH. By the way, why a not existed ItemID as ParentId, it doesn't make sense for me, in my opinion, the top level shouldn't have any ParentId, namely the ParentId should be null.

[amithegde]

---

@Eric_Zhang wrote:

---

@amithegde wrote:

---

@Eric_Zhang wrote:

---

@amithegde wrote:

Thanks @Eric_Zhang

apologies for being vague. By table I meant `table` visualization control. The idea is to be able to browse/see children of a selected item in a tabular format.

The answer you posted is very close. I am looking for a slight modification: If `1` selected on the slicer on the sample, table on the right should display all of the rows (Item ID 1 -6) as they are all children of 1. As of now it works for only one level, in the sense if 3 is selected on the slicer, it shows 3 and 5 which is level 1.

Thanks for the help.. 🙂

---

It works for only one level because I've followed the description in your original post. To achieve the new requirement, you can just remove the filter.

Table = 
FILTER (
    CROSSJOIN (
        SELECTCOLUMNS (
            Table1,
            "Itemid", Table1[ItemId],
            "pathLen", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        ),
        SELECTCOLUMNS (
            Table1,
            "Itemid2", Table1[ItemId],
            "ParentID2", Table1[ParentId],
            "path2", PATH ( Table1[ItemId], Table1[ParentId] ),
            "pathLen2", PATHLENGTH ( PATH ( Table1[ItemId], Table1[ParentId] ) )
        )
    ),
    PATHCONTAINS ( [path2], [Itemid] )
        //&& [pathLen2] - [pathLen]
        //<= 1
)

---

This works perfect, except that if any value other than `null` which is not in the list ItemId is provided for the first row's parent. Is that a limitation of the CrossJoin?

---

It is actually a limitation of PATH. By the way, why a not existed ItemID as ParentId, it doesn't make sense for me, in my opinion, the top level shouldn't have any ParentId, namely the ParentId should be null.

---

When parent Id can be from another table, while some of the parents can be from the current table, there can be parents which are not found on the current table as ItemId. For the simplified example above, I just mentioned `0` to represent some value which is not in the ItemId.

It's like this:

Table1:

Id | Title

100 | abc

200 | abcd

300 | abcde

Table 2:

Id | ParentId | Title

1 | 300 | xyz  --> parent from another table

2 | 1     | xyz1  --> parent from current table

3 | 2     |  xyz2 

4 | 100 | xyz3

[Eric_Zhang · Accepted solution]

@amithegde

Thanks for the clarification, however I don't get the point of such table design. Anyway, to bypass such limitation, add an extra column as the ParentID.

new ParentID = LOOKUPVALUE(Table3[ItemId],Table3[ItemId],Table3[ParentID])

[BhaveshPatel]

For creating parent child hierarchy in PowerBI, You can refer this bolg at here.  It would be better if you post your sample table and expected output for us to recreate a solution for you.

[amithegde]

thanks for the link @BhaveshPatel I got some ideas from it

[ranjeewas]

Hi @amithegde 

Did you find a way to solve your requirement? I have the same requirement but getting errors due to PATH limitations as mentioned by Eric above. If possible, can you please be kind enought to share the solution you applied?