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

[samira]

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 

[Zubair_Muhammad]

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

[Zubair_Muhammad]

@samira

Please see attached file as well

[samira]

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?

[Zubair_Muhammad · Accepted solution]

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