DAX query for completed and overdue status for measure and new column

[Shine_456]

Hi,

I have a query having training and task tables.

If user completes all trainings and start date is less than due date then status is completed.

If user completes one  training and one training is in progress and start date is less than due date then status is In progress.

If user Not started all trainings and start date is less than due date then status is Not started.

Need logic for count of completed,inprogress, notstarted and overdue.

Also need new column logic for completed, in progress, Not started and overdue.

Training Insights	Training Status
Tr 1	Users all completed Training	Completed
TR 2	Users all completed Training	Completed
TR 3	Few Users Completed	In progress
TR 4	Few Users Completed but training due date is passed	Overdue
Team Performance Insights
User 1	completed all assigned Training	Completed
User 2	completed all assigned Training	Completed
User 3	Completed only few assigned trainings	In progress
User 4	Completed few trainings  but trainings with over due	Overdue

Tr table

org_id	name	type_id	duration	due_date	row_status_id	created_by_id	created_at	updated_at	id
200003	Training 6	100	3	15-02-2022	1	600027	14-02-2022	14-02-2022	100
200003	Training 7	100	5	25-02-2022	1	600027	14-02-2022	14-02-2022	103
200003	Training 8	100	5	04-03-2022	1	600027	23-02-2022	23-02-2022	106
200003	Test Training Requirement-1	100	1	24-03-2022	1	600027	24-02-2022	24-02-2022	109
200003	Test Training Requirement-2	103	3	30-03-2022	1	600027	24-02-2022	24-02-2022	112
200003	Test Training Requirement-3	106	6	06-04-2022	1	600027	24-02-2022	24-02-2022	115
200003	TR-4	103	5	02-04-2022	1	600030	24-02-2022	24-02-2022	118

Task table

org_id	training_requirement_id	user_id	status	progress	row_status_id	id
200003	103	600027	COMPLETED	100	1	100
200003	109	600029	NOT_STARTED	0	1	103
200003	109	600030	COMPLETED	100	1	106
200003	115	600027	NOT_STARTED	0	2	109
200003	115	600028	NOT_STARTED	0	2	112
200003	115	600030	COMPLETED	100	1	115
200003	112	600027	IN_PROGRESS	50	1	118
200003	115	600027	IN_PROGRESS	50	1	121
200003	118	600027	COMPLETED	100	1	124

Plz help me on this.

[Anonymous · Accepted solution]

Hi @Shine_456,

I checked your sample data and find it does not include the detailed records mapping of user and training information.

The basic logic of the coding formula:

1, Add a calculated column to the user table to get the records from the table that stored user training information.

2. Get the distinct training id from the above steps and compare it with the training table id list and use these results to remark the user's status. (no records of the current user: not start, the id less than training table id: in progress, two id lists has same items: complete)

INTERSECT function (DAX) - DAX | Microsoft Docs

EXCEPT function (DAX) - DAX | Microsoft Docs

3. Add a calculated column to the training table based on user table records to remark status.

Regards,

Xiaoxin Sheng