locked
Count Parent records without Children records RRS feed

  • Question

  • Hello

    I’ve got two related tables in my model Employees and Employee_Benefits, related on the Employee_ID (text field). What I need to do is  count the current ‘In Work’ Employees in the Employees table that don’t have corresponding records in the Employee_Benefits table

    I’ve tried various counts, isBlank, AllExcept but cannot seem to get it to work. I can count the Employees that have related records, but those without have got me stumped?

    EVALUATE ROW (     "ABC", CALCULATE (         COUNTAX (FILTER ( Employees, [Employee_Status] = "In Work" ), Employees [Employee_ID]),         RELATEDTABLE (Employee_Benefits)     ) )

    All suggestions welcome

    Many thanks

    Roy


    Tuesday, July 31, 2018 1:15 PM

Answers

  • Hi Roy,

    Thanks for your question.

    Darren has given you a solid solution for this issue. Another possibility is to use contains function to get the desired results, see below sample DAX formula:

    EVALUATE ROW ( "ABC",
    COUNTAX (
        FILTER (
            Employees,
            [Employee_Status] = "In Work"
                && NOT CONTAINS (
                    VALUES ( Employee_Benefits[Employee_ID] ),
                    Employee_Benefits[Employee_ID], Employees[Employee_ID]
                )
        ),
        Employees[Employee_ID]
       )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, August 1, 2018 11:03 AM
    • Marked as answer by ryand09 Saturday, August 4, 2018 11:38 AM
    Wednesday, August 1, 2018 1:50 AM

All replies

  • You could do this by just subtracting the employees with benefits from the total employees in work

    eg.

    EVALUATE ROW (     "ABC",
    CALCULATE( 
    COUNTAX (FILTER ( Employees, [Employee_Status] = "In Work" ), Employees [Employee_ID])) )
    -

    CALCULATE (         
    COUNTAX (FILTER ( Employees, [Employee_Status] = "In Work" ), Employees [Employee_ID]),         RELATEDTABLE (Employee_Benefits)     ) )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 31, 2018 8:50 PM
  • Hi Roy,

    Thanks for your question.

    Darren has given you a solid solution for this issue. Another possibility is to use contains function to get the desired results, see below sample DAX formula:

    EVALUATE ROW ( "ABC",
    COUNTAX (
        FILTER (
            Employees,
            [Employee_Status] = "In Work"
                && NOT CONTAINS (
                    VALUES ( Employee_Benefits[Employee_ID] ),
                    Employee_Benefits[Employee_ID], Employees[Employee_ID]
                )
        ),
        Employees[Employee_ID]
       )
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, August 1, 2018 11:03 AM
    • Marked as answer by ryand09 Saturday, August 4, 2018 11:38 AM
    Wednesday, August 1, 2018 1:50 AM
  • Excellent solution

    Thanks Willson

    Saturday, August 4, 2018 11:38 AM