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

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

amithegde — Sat, 08 Oct 2016 00:14:28 GMT

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.

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

BhaveshPatel — Sun, 09 Oct 2016 06:20:56 GMT

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.

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

Eric_Zhang — Tue, 11 Oct 2016 08:16:50 GMT

@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
)

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

amithegde — Tue, 11 Oct 2016 17:58:00 GMT

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

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

amithegde — Tue, 11 Oct 2016 17:58:57 GMT

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

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

Eric_Zhang — Wed, 12 Oct 2016 01:40:00 GMT

@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
)

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

amithegde — Wed, 12 Oct 2016 04:53:25 GMT

@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?

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

Eric_Zhang — Wed, 12 Oct 2016 05:58:59 GMT

@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.

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

amithegde — Wed, 12 Oct 2016 06:10:59 GMT

@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

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

Eric_Zhang — Wed, 12 Oct 2016 06:39:23 GMT

@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])

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

ranjeewas — Thu, 27 Jun 2024 13:57:30 GMT

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?