Except function with a filter - unlogged employees

[vinaydavid]

Hi All,

Want to achieve the below result from the 2 unrelated tables.

Table 1 - Logged employees list with logdate

Table 2 - Complete list of employees (without logdate)

Everyday I get a list of employee who logged their hours in Table 1.

There are a couple of users who do not log their entries.

So, I want to get the list of missing employees, from Table 2 who missed to log their entries in a selected logdate.

Table 1
Emp	Logdate
A	18/05/2020
B	18/05/2020
C	18/05/2020
D	18/05/2020

Table 2

A

B

C

D

E

F

G

If I run report for 18th May, I should get the below users list, to send a notification to these users.

Expected result: E,F & G

Thanks in advance!

[v-xicai]

Hi   @vinaydavid ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[vinaydavid]

@Ashish_Mathur Thanks for your steps. I have tried but not sure why it dint work the way I wanted.

Thanks every one for giving me different options, but still haven't got the desired result.

@v-xicai 

Amy, here is the scenario

Table 1 contains the log data of all the employees (maintains history) (fields - employee name, logdate, many other columns)

Table 2 contains the total employee name list (unique) with start and end date of employement.

Both the tables are unrelated.

Date table exists with join to logdate of Table 1.

The report uses a slicer based on Logdate to select a date or range.

Then I need the report to compare Table 1 and Table 2, to get the list of missing employees from Table 2 (if any), so that I can follow up with the missing employees as to why they dint log their time.

Looks simple theoretically, but I am unable to achieve in practical.

Let me know, if I am not clear in explaning the requirement.

Thanks in advance!

Dave

[Ashish_Mathur]

Hi,

My method should have worked.  I'll need to see your PBI file.  In that file, please show me very clearly where the problem is and what the result should be.

[Ashish_Mathur]

Hi,

Try this

1. Create a Calendar Table and build a relationship from the Logdate column of Table1 to the Date column of the Calendar Table

2. Create a relationship from the Emp column of Table1 to the Emp column of Table2.  There should not be duplicate Emp's in Table2

2. Create a slicer from the Date column of the Calendar Table

3. To yoru visual, drag the Emp from Table2

4. Write this measure

=Countrows(Table1)

Hope this helps.

[v-xicai]

Hi @vinaydavid ,

You may create a new calculated table like DAX below.

Expected result= EXCEPT (VALUES ('Table 2'[Emp]), VALUES ('Table 1'[Emp]))

Best Regards,

Amy 

Community Support Team _ Amy

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

[vinaydavid]

@v-xicai 

Table 1 contains history. So, is it possible to restrict using FILTER in EXCEPT?

@amitchandak 

Tried using the measures you suggested, but not successful.

How are these measures cross checking Table2?

NOTE: There is no relation between Table1 and Table2. Avoided because of other calculations.

[amitchandak]

@vinaydavid , Try

Measure = 
CALCULATE(COUNTX(filter(Table2,Table2[Emp] in EXCEPT(ALL(Table2[Emp]),ALL('Table 1'[Emp]))),[Emp]))

[negi007]

There is a workaround for this. You can view the desired output by using matrix visual. I have attached below screenshot

[amitchandak]

@vinaydavid , try like

measure = calculate(count(table1[Logdate]))+0
measure2 = calculate([measure], filter(table1,[measure] =0)

Or

measure = calculate(count(table1[Logdate]))
measure2 = calculate([measure], filter(all(table1),isblank([measure]))

Measure 2 is one you are looking for

[nandukrishnavs]

@vinaydavid ,

You can use the EditQuery Merge option.

Table2 merge with Table1 using employeeID. (All from first matching from second). Then expand the merged table and filter the blank dates. 

Another Option, create a calculated column in Table2, using lookupvalue() you can search the value in Table1. If it is return blank() that means that the employee is missing in Table1.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[vinaydavid]

@nandukrishnavs ,

Thanks for your reply.

I can't go with the 1st option, as the report gets filtered using a slicer.

So I need to filter the userlist based on a logdate, and then compare with Table 2.

For 2nd option, as I am a beginner in dax, not sure if we can use lookupvalue and filter or some other function to restrict for a selected date.