Get number count of column that are not in other column/table

[RanHo]

Good day!

  Need help here , how to get count of EVALUATION ID that are not in TABLE TEST?
Here's my sample data with 3 tables:

TABLE EVALUATION		TABLE EMPLOYEE			TABLE TEST
ID	EMPLOYEE_ID	ID	EMPLOYEE_NO		ID	EMPLOYEE_NO
1001	1	1	TC-001		2001	TC-002
1002	3	2	TC-002		2002	TC-004
1003	6	3	TC-003		2003	TC-005
1004	7	4	TC-004		2004	TC-008
1005	10	5	TC-005		2005	TC-009
1006		6	TC-006		2006
1007		7	TC-007		2007
1008		8	TC-008		2008
1009		9	TC-009		2009
1010		10	TC-010		2010

thanks in advance!

~RAN

[rajendraongole1 · Accepted solution]

Hi @RanHo - i have created a mapping as below:

Relationship 1: TABLE EVALUATION[EMPLOYEE_ID] links to TABLE EMPLOYEE[ID].

This relationship helps connect evaluations to employees.
Relationship 2: TABLE EMPLOYEE[EMPLOYEE_NO] links to TABLE TEST[EMPLOYEE_NO].

This relationship helps identify which employees have corresponding entries in TABLE TEST.

CountMissingEvaluationIDs =

CALCULATE(

    COUNTROWS('TABLE EVALUATION'),

    FILTER(

        'TABLE EVALUATION',

        VAR EmpNo = LOOKUPVALUE(

            'TABLE EMPLOYEE'[EMPLOYEE_NO],

            'TABLE EMPLOYEE'[ID], 'TABLE EVALUATION'[EMPLOYEE_ID]

        )

        RETURN

            ISBLANK(

                LOOKUPVALUE(

                    'TABLE TEST'[ID],

                    'TABLE TEST'[EMPLOYEE_NO], EmpNo

                )

            )

    )

)

Hope this helps.

[shafiz_p · Accepted solution]

Hi @RanHo  Don't know your relationship among the tables. I have considered this:

I think you need to count employee which are in the evaluation but not in the Test. If this is the case, try this:

MissingEvaluationCount = 
COUNTROWS(
    FILTER(
        EMPLOYEE,
        NOT EMPLOYEE[EMPLOYEE_NO] IN VALUES(TEST[EMPLOYEE_NO])
    )
)

I have this output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[shafiz_p · Accepted solution]

Hi @RanHo  Don't know your relationship among the tables. I have considered this:

I think you need to count employee which are in the evaluation but not in the Test. If this is the case, try this:

MissingEvaluationCount = 
COUNTROWS(
    FILTER(
        EMPLOYEE,
        NOT EMPLOYEE[EMPLOYEE_NO] IN VALUES(TEST[EMPLOYEE_NO])
    )
)

I have this output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[RanHo]

TABLE EVAL is connected to TABLE EMPLOYEE then TABLE EMPLOYEE to TABLE TEST.

[FreemanZ]

hi @RanHo ,

who is on the one side in the relationships?

[rajendraongole1 · Accepted solution]

Hi @RanHo - i have created a mapping as below:

Relationship 1: TABLE EVALUATION[EMPLOYEE_ID] links to TABLE EMPLOYEE[ID].

This relationship helps connect evaluations to employees.
Relationship 2: TABLE EMPLOYEE[EMPLOYEE_NO] links to TABLE TEST[EMPLOYEE_NO].

This relationship helps identify which employees have corresponding entries in TABLE TEST.

CountMissingEvaluationIDs =

CALCULATE(

    COUNTROWS('TABLE EVALUATION'),

    FILTER(

        'TABLE EVALUATION',

        VAR EmpNo = LOOKUPVALUE(

            'TABLE EMPLOYEE'[EMPLOYEE_NO],

            'TABLE EMPLOYEE'[ID], 'TABLE EVALUATION'[EMPLOYEE_ID]

        )

        RETURN

            ISBLANK(

                LOOKUPVALUE(

                    'TABLE TEST'[ID],

                    'TABLE TEST'[EMPLOYEE_NO], EmpNo

                )

            )

    )

)

Hope this helps.

[RanHo]

@rajendraongole1 I'll try this and get back to you.

[v-xuxinyi-msft · Accepted solution]

Hi @RanHo 

Thanks for the reply from FreemanZ , rajendraongole1 and shafiz_p .

Do the methods solve your problem? If so, could you please mark helpful answers as solutions? This will help more users who are facing the same or similar difficulties. Thank you!

If the problem is not solved , please try the following measure.

Count = 
COUNTROWS (
    EXCEPT (
        SELECTCOLUMNS ('TABLE EVALUATION', "EVALEVALUATION", 'TABLE EVALUATION'[ID]),
        SELECTCOLUMNS ('TABLE TEST', "EVALEVALUATION1", 'TABLE TEST'[ID])
    )
)

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @RanHo ,

how are the three tables related?