Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amithegde
Frequent Visitor

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

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.

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

Capture.PNG

 

View solution in original post

9 REPLIES 9
Eric_Zhang
Employee
Employee

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

Capture.PNG

 

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


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


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


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

 


@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

 

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

Capture.PNG

 

BhaveshPatel
Community Champion
Community Champion

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.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.