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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yes
Frequent Visitor

Dax Calculated column for indirect reports

Good Morning,

 

I have career history data in a table and I'm trying to calculate how many indirect reports each manager has. I've done this for direct reports but indirect is proving more challenging. There can be up to 7 links in chain of command.

 

The challenge is that this data is monthly, typically the last day of the month, 31/03/2023, 30/04/2023, 31/05/2023 etc added to the bottom of the table and I'm trying to calculate how many indirect reports each manger has in that month, so that the front end visuals can be changed to show as is for different months.

 

The data is all in the same table with columns:

Person Number 

Manager Person Number

Status Date

# Direct Reports

 

Thanks

3 REPLIES 3
tamerj1
Super User
Super User

Hi @yes 
Please provide some sample data along with the expected result.

yes
Frequent Visitor

Hi,

Please see below data to illustrate the format. I've got the column for Direct Reports calculating but it's the column for Indirect reports that I'm struggling with.

 

Person NumberManager Person NumberStatus DateDirect ReportsIndirect Reports
1001 31/05/202327
1002100131/05/202321
1003100131/05/202330
1004100231/05/202300
1005100231/05/202311
1006100331/05/2023  
1007100331/05/2023  
1008100331/05/2023  
1009100531/05/2023  
1010100931/05/2023  
1001 30/04/2023  
1002100130/04/2023  
1013100130/04/2023  
1004100230/04/2023  
1005100230/04/2023  
1006101330/04/2023  
1007101330/04/2023  
1008101330/04/2023  
1009100530/04/2023  
1010100930/04/2023  
1001 31/03/2023  
1002100131/03/2023  
1003100131/03/2023  
1004100231/03/2023  
1005100231/03/2023  
1006100331/03/2023  
1007100331/03/2023  
1008100331/03/2023  
1009100531/03/2023  
1010100831/03/2023  
yes
Frequent Visitor

Edit to add i have tried using PATH and that does exactly what I want it to do but doesn't have the option to filter to only rows with the same status date.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.