List records with no entries in related table

[IanCockcroft]

Hi guys,

we have an employee table and related to this is an access control table with a one to many relationship.

I have a table that shows all the employees that accessed the building by date. count by month.

I need to show in this table, anyone who didnt access the building in that month(worked from home).

e.g

Employee
emploeeID	name	bu
1	Joe	Fron Office
2	Ross	Banking
3	Monica	Treasury
4	Rachel	Banking
5	Phodea	Treasury
6	Chandler	Banking

access control
emploeeID	Event date
1	2021-10-01
1	2021-10-02
1	2021-10-03
1	2021-10-04
1	2021-10-05
1	2021-10-06
2	2021-10-01
2	2021-10-02
2	2021-10-03
2	2021-10-04
2	2021-10-05
2	2021-10-06
3	2021-10-03
3	2021-10-04
3	2021-10-05
3	2021-10-06
5	2021-10-01
5	2021-10-02
5	2021-10-03
5	2021-10-04
5	2021-10-05
5	2021-10-06
5	2021-10-07
6	2021-10-05
6	2021-10-06

the result should be:

1	Joe	Fron Office	6
2	Ross	Banking	6
3	Monica	Treasury	4
4	Rachel	Banking	0
5	Phodea	Treasury	7
6	Chandler	Banking	2

Although Rachel never accessd the building and has no records in the access control table, i need to report the fact.

i have tried  few things, some DAX. trying to change the table relationships etc

any idea?

thanks a mil guys

[VahidDM · Accepted solution]

Hi @IanCockcroft 

Try this code to add a column to the Employee table:

Column =
VAR _A =
    CALCULATE(
        COUNTROWS( 'access control' ),
        'access control'[emploeeID] = EARLIER( Employee[emploeeID] )
    )
RETURN
    IF( ISBLANK( _A ), 0, _A )

the output:

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

Appreciate your Kudos!!

[IanCockcroft]

Thanks @VahidDM 

the DAX is 

               # of Office Access MTD =

               VAR _A =

               CALCULATE(

                 COUNTROWS( 'ACE Grouped'),

                    'ACE Grouped'[EmployeeID] = max( 'Employee Details'[RMB Employee Number])

                    )

              RETURN

             IF( ISBLANK( _A ), 0, _A )

I need to do another measure that does a SUM of the number of employees that  didnt access the office at least 4 times by Business Unit.

Any idea?

[IanCockcroft]

Thanks Vahid,

seems to be heading in the right direction.

I do get an issue with EARIER()

any idea why?

thanks a mil  again

ps: it needs to   be dynamic depending on month selected, think thats why i get the error.