cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
samira Frequent Visitor
Frequent Visitor

Parent -child hierarchy : recursively show all reports on PowerBI desktop

DAX.PNG

 

I want to show all the reports under a manager, not just the direct reports.

If I select Karl , I want to see Jeremy, Marc, Serge, Rick, Cynthia, Lola and Elvi. 

 

I looked everywhere and modified the DAX functions without success.

All I can find was the direct reports or the higest level parent. 

 

My data comes from kusto which doesn't have recursive CTE functions like SQL 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Parent -child hierarchy : recursively show all reports on PowerBI desktop

@samira

 

Try this modification..assuming your ID columns are text

See attached file as well

 

Children =
VAR mypath = [MyPath]
VAR AllChildIDs =
    CONCATENATEX (
        FILTER (
            Table1,
            PATHCONTAINS ( [MyPath], EARLIER ( [Id] ) )
                && VALUE ( [Id] ) > VALUE ( EARLIER ( Table1[Id] ) )
        ),
        [Id]
    )
RETURN
    IF (
        AllChildIDs <> BLANK (),
        CONCATENATEX (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    GENERATESERIES ( 1, LEN ( AllChildIDs ) ),
                    "Child IDs", MID ( AllChildIDs, [Value], 1 )
                ),
                "Child Names", LOOKUPVALUE ( Table1[Name], Table1[Id], [Child IDs] )
            ),
            [Child Names],
            ", "
        )
    )
Try my new Power BI game Cross the River

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Parent -child hierarchy : recursively show all reports on PowerBI desktop

@samira

 

 

Try these 2 calculated columns

 

MyPath =
PATH ( Table1[Id], Table1[Parent ID] )

 

Children =
VAR mypath = [MyPath]
VAR AllChildIDs =
    CONCATENATEX (
        FILTER (
            Table1,
            PATHCONTAINS ( [MyPath], EARLIER ( [Id] ) )
                && [Id] > EARLIER ( Table1[Id] )
        ),
        [Id]
    )
RETURN
    IF (
        AllChildIDs <> BLANK (),
        CONCATENATEX (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    GENERATESERIES ( 1, LEN ( AllChildIDs ) ),
                    "Child IDs", MID ( AllChildIDs, [Value], 1 )
                ),
                "Child Names", LOOKUPVALUE ( Table1[Name], Table1[Id], VALUE ( [Child IDs] ) )
            ),
            [Child Names],
            ", "
        )
    )
Try my new Power BI game Cross the River
Super User
Super User

Re: Parent -child hierarchy : recursively show all reports on PowerBI desktop

@samira

 

Please see attached file as well

 

parenthcilren.png

Try my new Power BI game Cross the River
samira Frequent Visitor
Frequent Visitor

Re: Parent -child hierarchy : recursively show all reports on PowerBI desktop

I get this error when I try it on my data : Function LOOKUPVALUE does not support comapring values of type Text with values of type Number.

 

all my data is of type string and when I tried changing Query1[Id] to type int, it still failed. 

 LOOKUPVALUE ( Query1[Name], Query1[Id] ) <= should query1[Id] be of type Number?

Super User
Super User

Re: Parent -child hierarchy : recursively show all reports on PowerBI desktop

@samira

 

Try this modification..assuming your ID columns are text

See attached file as well

 

Children =
VAR mypath = [MyPath]
VAR AllChildIDs =
    CONCATENATEX (
        FILTER (
            Table1,
            PATHCONTAINS ( [MyPath], EARLIER ( [Id] ) )
                && VALUE ( [Id] ) > VALUE ( EARLIER ( Table1[Id] ) )
        ),
        [Id]
    )
RETURN
    IF (
        AllChildIDs <> BLANK (),
        CONCATENATEX (
            ADDCOLUMNS (
                ADDCOLUMNS (
                    GENERATESERIES ( 1, LEN ( AllChildIDs ) ),
                    "Child IDs", MID ( AllChildIDs, [Value], 1 )
                ),
                "Child Names", LOOKUPVALUE ( Table1[Name], Table1[Id], [Child IDs] )
            ),
            [Child Names],
            ", "
        )
    )
Try my new Power BI game Cross the River

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)