topic Re: Dax Calculated column for indirect reports in DAX Commands and Tips

Dax Calculated column for indirect reports

Anonymous — Thu, 01 Jun 2023 08:07:55 GMT

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

Re: Dax Calculated column for indirect reports

Anonymous — Thu, 01 Jun 2023 08:10:08 GMT

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.

Re: Dax Calculated column for indirect reports

tamerj1 — Thu, 01 Jun 2023 10:51:00 GMT

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

Re: Dax Calculated column for indirect reports

Anonymous — Thu, 01 Jun 2023 14:37:47 GMT

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