cancel
Showing results 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

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
Super User

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

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 Number Manager Person Number Status Date Direct Reports Indirect Reports 1001 31/05/2023 2 7 1002 1001 31/05/2023 2 1 1003 1001 31/05/2023 3 0 1004 1002 31/05/2023 0 0 1005 1002 31/05/2023 1 1 1006 1003 31/05/2023 1007 1003 31/05/2023 1008 1003 31/05/2023 1009 1005 31/05/2023 1010 1009 31/05/2023 1001 30/04/2023 1002 1001 30/04/2023 1013 1001 30/04/2023 1004 1002 30/04/2023 1005 1002 30/04/2023 1006 1013 30/04/2023 1007 1013 30/04/2023 1008 1013 30/04/2023 1009 1005 30/04/2023 1010 1009 30/04/2023 1001 31/03/2023 1002 1001 31/03/2023 1003 1001 31/03/2023 1004 1002 31/03/2023 1005 1002 31/03/2023 1006 1003 31/03/2023 1007 1003 31/03/2023 1008 1003 31/03/2023 1009 1005 31/03/2023 1010 1008 31/03/2023
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.