topic Re: Count Rows on another table based on attributes on main table in DAX Commands and Tips

Count Rows on another table based on attributes on main table

aflintdepm — Mon, 15 Jul 2024 17:00:27 GMT

I have 2 tables, one is a list of employees with employment status and the other is a list of tasks assigned to those employees with the task status. Everthing is linked by EE_ID, but I did have to create a table of just unique values to avoid Many:Many between the 2 source tables, so my relationship looks like this:
Task Table *->1 Unique EE_ID 1<-* EE Status

I need create a measure that tells me the list of Incomplete tasks when an Employee is Terminated so those tasks can be reassigned.

I would like to present the results in a table or matrix.

Desired Ouput:

EE_ID	Name	Status	Task Count
1111	John Smith	Terminated	2
2222	Bob Jones	Terminated	0

EE_Status:

EE_ID	Name	Status
1111	John Smith	Terminated
2222	Bob Jones	Terminated
3333	Jane Doe	Active

Task table:

EE_ID	Task_Name	Task_Status
1111	Visit	Complete
1111	Visit	Incomplete
1111	Eval	Incomplete
2222	Visit	Complete
2222	Visit	Complete
2222	Eval	Complete
3333	Visit	Complete
3333	Visit	Incomplete
3333	Eval	Incomplete

Re: Count Rows on another table based on attributes on main table

Samarth_18 — Mon, 15 Jul 2024 17:24:31 GMT

Hi aflintdepm ,

You could use below measure -

Measure = COUNTROWS ( FILTER ( 'Task table', 'Task table'[EE_ID] = MAX ( EE_Status[EE_ID] ) && 'Task table'[Task_Status] = "Incomplete" && MAX ( EE_Status[Status] ) = "Terminated" ) ) + 0

output -

Re: Count Rows on another table based on attributes on main table

aflintdepm — Tue, 16 Jul 2024 17:15:35 GMT

Samarth_18
Thank you for the response. Can you please clarify the function of the MAX in the measure? Other than that, I believe I understand the syntax

Re: Count Rows on another table based on attributes on main table

Sahir_Maharaj — Fri, 19 Jul 2024 15:40:28 GMT

Hello aflintdepm,

Can you please try the following approach:

IncompleteTasksForTerminated = VAR TerminatedEmployees = FILTER ( 'EE_Status', 'EE_Status'[Status] = "Terminated" ) VAR IncompleteTasks = FILTER ( 'Task Table', 'Task Table'[Task_Status] = "Incomplete" ) VAR Result = COUNTROWS ( FILTER ( 'Task Table', 'Task Table'[EE_ID] IN VALUES (TerminatedEmployees[EE_ID]) && 'Task Table'[Task_Status] = "Incomplete" ) ) RETURN Result

Hope this helps!

Re: Count Rows on another table based on attributes on main table

Samarth_18 — Sat, 20 Jul 2024 04:21:53 GMT

aflintdepm , It's just to pick a value of EE_ID, Status from the current context.