How to show on report if any employee not ent4er data daily

[Anonymous]

Hi. 

I'm managing my team task log and everyone in the team should enter data about the task they have done daily in this table:

Date

Sr. No.

Client

Task Title

Task Details

E.Time

A.Time

Total Hrs. (For the day)

Task Status

Name

Is there a way to write Dax function to show the name and date on the report of any employee who didn't enter data for any day in the week.

Thanks a lot

[Anonymous]

The table isn't clear so I will write it here:
Date | Sr.No | Client | Task Title | Task Detail | E.Time | A.Time | Total Hours | Task Status | Name

[Migasuke]

Hi @Anonymous ,
To make this work, you need 2 tables. One table with all names and then table with records.
Once you have this, do following:

1. Create a relationship between those tables (based on names or some personal ID)

2. Create a measure which evaluates blank inputs. Something like:

Check = IF(COUNTROWS(Records)>0,BLANK(),"No Record")

3.Create a table where yout take Name from Employees table and combine with measures.

In my case, Lucy is the only one who does not have any record in Records table.

[Anonymous]

Hi. Thank you for your response. 

I'm pretty new to DAX so could you please help me explain the logic behind the code. Especially this part: COUNTROWS(Records)>0 why would you use >0.

Thanks again for expaining this.

[Migasuke]

COUNTROWS calculate number of records. If result is zero, it means there is no record.

So the whole logic is,

If number of records is bigger than zero we have at least one record. If the result is zero, we do not have any record.

Because we have the IF statement, we then decide what to do with the logic:

more than zero --- returns blank (i am not interested in people who have record)

zero --- returns text "No record" (we are interested in people who do not have any)

Please let me know if it makes sense to you.

[Anonymous]

Thanks for the explaination. But I think I haven't explain it clearly. So my team has more than 20 people and my boss one they to record their daily task everyday in only one sheet. I only put the column headers but there are few hundred rows of data as of now. Everyday, there should be mroe than 20 new entry and we want to know if there is a way to find out if any employee haven't entered anything yet.

[Anonymous]

Hi @Anonymous ,

According to your statement, I think you want to find the employees who didn't write the record each week. According to the column header you provided, I think there should be [Name] column to determind which user has entered this record and [Date] column to determind which day they entered this record.

Here I suggest you to create a DimEmployee table and a DimDate table to help you achieve your goal.

My Sample:

Data table:

DimEmployee:

DimDate:

DimDate = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeedNum", WEEKNUM ( [Date], 2 )
)

Relationship:

Measure:

Employee who didn't write the record in this week = 
VAR _Values =
    CALCULATETABLE (
        VALUES ( DimEmployee[Name] ),
        FILTER ( DimEmployee, NOT ( DimEmployee[Name] IN VALUES ( 'Table'[Name] ) ) )
    )
RETURN
    CONCATENATEX ( _Values, [Name], " " )

Result is as below.

Best Regards,
Rico Zhou

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

[Migasuke]

Hi @Anonymous ,
can you provide example of your outcome? Printscreen in simple excel is enough.

[Anonymous]

I don't have an example yet. I was thinking if we have a way to write DAX function show that it will show on the dashboard and report that which employee haven't enter new data for any date of a week. For example, each of the employee should have at least 5 new rows a week and if employee A didn't enter any data for any day of a week such as Monday, Tuesday, etc. the report will show the name of employee which is A and the date which that employee didn't enter any new data.

[Migasuke]

The measure I showed above does exactly what you mentioned. Just add to the table Date/Week or anything else you are interested in.

If I add in my example table date - I can see exactly what date Lucy did not fill in.