topic Re: Identify names if there is a missing entry in another table in DAX Commands and Tips

Identify names if there is a missing entry in another table

efstel — Tue, 15 Oct 2024 02:29:02 GMT

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?

Re: Identify names if there is a missing entry in another table

Jihwan_Kim — Tue, 15 Oct 2024 03:39:29 GMT

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

Re: Identify names if there is a missing entry in another table

shafiz_p — Tue, 15 Oct 2024 03:57:30 GMT

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

Re: Identify names if there is a missing entry in another table

quantumudit — Tue, 15 Oct 2024 03:59:40 GMT

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

Re: Identify names if there is a missing entry in another table

efstel — Thu, 24 Oct 2024 01:18:48 GMT

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,

)

RETURN

CONCATENATEX (

_result,

'bihj HeavyJob_JM_BUEmployee'[EmployeeCode],

", "

)