Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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 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], ", " ) )
Please see attached file as well
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?
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], ", " ) )