Forum Discussion
Helper IIIExcept function with a filter - unlogged employees
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!
11 Replies
- nandukrishnavs
Community Champion
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 🙂
vinaydavidThanks 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.
- amitchandak
Super User
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
- negi007
There is a workaround for this. You can view the desired output by using matrix visual. I have attached below screenshot
v-xicaiCommunity Support
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
Table 1 contains history. So, is it possible to restrict using FILTER in EXCEPT?
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]))
- 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 ,
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.
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.
