Identify names if there is a missing entry in another table

[efstel]

I have two tables. One table contains a list of names (Employee). The other table (Time) is populated every day with names and hours worked. What I want to find out is if someone's name is completely missing for any given date. In other words, if somebody forgot to report that person's time, there would be no record of that person on that date within the Time table.  How do I create a Measure that can identify if a person's name is missing?

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Total work hour: = 
SUM( work_hour_fact[work-hour] )

NOT work employee: = 
VAR _t =
    SUMMARIZE (
        work_hour_fact,
        employee_dim[employee]
    )
VAR _all =
    ALL ( employee_dim[employee] )
VAR _result =
    EXCEPT (
        _all,
        _t
    )
RETURN
    CONCATENATEX (
        _result,
        employee_dim[employee],
        ", "
    )

EXCEPT function (DAX) - DAX | Microsoft Learn

NOT work employee: =
VAR _t =
    SUMMARIZE (
        work_hour_fact,
        employee_dim[employee]
    )
VAR _all =
    ALL ( employee_dim[employee] )
VAR _result =
    EXCEPT (
        _all,
        _t
    )
RETURN
    CONCATENATEX (
        _result,
        employee_dim[employee],
        ", "
    )

[quantumudit]

Hello @efstel 

To assist you effectively, it would be helpful to have more context regarding your query, such as a sample dataset, the structure of your data model, and an example of the desired outcome. This information will enable me to understand your problem better.

Based on my initial understanding of your query, you can create a calculated table using the DAX formula provided below. This table will enumerate the employee names and the corresponding dates where no hours are logged.

Missed Logging Table = 
 	SELECTCOLUMNS(
		FILTER(
			ADDCOLUMNS(
				CROSSJOIN(
					VALUES(EmployeeTbl[EmployeeName]),
					VALUES(LogTbl[Date])
				),
				"LoggedHours", CALCULATE(SUM(LogTbl[HoursWorked]))
			),
			[LoggedHours] = BLANK()
		),
		"Employee Name", [EmployeeName],
		"Missed Logging Date", [Date]
	)

Subsequently, you can display the data in the following manner:

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[shafiz_p · Accepted solution]

Hi @efstel ,
Considering you have table and relationship similar as shown below:

Employee Table: 

TimeTable:

Calendar Table:

Relationship :

Count the missing employee, try below code:

MissingNamesCount = 
CALCULATE(
    COUNTROWS(Emp),
    EXCEPT(
        Emp,
        VALUES(TimeTab[Name])
    )
)

Desired Output:

Concatenating all the missing names in time table, try below code:

MissingNames = 
VAR AllEmployees = VALUES(Emp[Name])
VAR ReportedEmployees = CALCULATETABLE(VALUES(TimeTab[Name]), ALL(TimeTab), TimeTab[Date] = MAX(TimeTab[Date]))
VAR MissingEmployees = EXCEPT(AllEmployees, ReportedEmployees)
RETURN 
IF(
    HASONEVALUE(CalTab[Date]),
    CONCATENATEX(MissingEmployees, [Name], ", ")
)

Desired output:

Hope this helps!!

If this solved your problem, please accept it as a solution.

Best Regards,
Shahariar Hafiz

[Jihwan_Kim · Accepted solution]

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Total work hour: = 
SUM( work_hour_fact[work-hour] )

NOT work employee: = 
VAR _t =
    SUMMARIZE (
        work_hour_fact,
        employee_dim[employee]
    )
VAR _all =
    ALL ( employee_dim[employee] )
VAR _result =
    EXCEPT (
        _all,
        _t
    )
RETURN
    CONCATENATEX (
        _result,
        employee_dim[employee],
        ", "
    )

EXCEPT function (DAX) - DAX | Microsoft Learn

NOT work employee: =
VAR _t =
    SUMMARIZE (
        work_hour_fact,
        employee_dim[employee]
    )
VAR _all =
    ALL ( employee_dim[employee] )
VAR _result =
    EXCEPT (
        _all,
        _t
    )
RETURN
    CONCATENATEX (
        _result,
        employee_dim[employee],
        ", "
    )

[efstel]

Almost... I'm getting lots of names, but I also have a column within the employee master table that has a true/false statement. "IsActive" let's me know whether they are currnently employeed by us or not.  But when I filter it, it's not filtering. 

NOT work employee: =

VAR _t =

    SUMMARIZE (

        'bihj HeavyJob_TimecardCostcodeLaborResource',

        'bihj HeavyJob_JM_BUEmployee'[EmployeeCode]

    )

VAR _all =

    ALL ('bihj HeavyJob_JM_BUEmployee'[EmployeeCode])

VAR _result =

    EXCEPT (

        _all,

        _t

    )

RETURN

    CONCATENATEX (

        _result,

        'bihj HeavyJob_JM_BUEmployee'[EmployeeCode],

        ", "

    )