Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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], ", " ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |