DAX formula to calculate days worked out of possible days

[cmorrow]

Simplifying things, I have a report using DirectQuery with 2 tables: Date and EmployeeTimesheet.  

Date columns: DayeKey, Date, Year, Month, DayOfWeek, isWeekday

EmployeeTimesheet columns: EmployeeID, DateKey, TaskID, MinutesWorked

Example:

EmployeeA, 20190101, Task1, 15

EmployeeA, 20190102, Task1, 15

EmployeeA, 20190102, Task2, 60

EmployeeA, 20190103, Task2, 90

What I'm trying to calculate (and graph) is the number of days out of all available weekdays where the employee worked at least 60 mins.  So there are 23 weekdays in January (isWeekday=1).  Therefore, in January, EmployeeA worked 2 days out of 23, or 8%.  

I've tried defining a measure as below, and using it as a filter (Daily Time Total > 60), but it aggregates the hours to the Employee level and doesn't filter properly.

Daily Time Total =
CALCULATE(
SUM('EmployeeTimesheet'[MinutesWorked]),
ALLSELECTED('EmployeeTimesheet'[DateKey])
)

[Greg_Deckler]

Are you looking for something like NETWORKDAYS?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362

[cmorrow]

No, I don't think so.  When I try to translate that to my needs, it doesn't work.  For example, if I were to do this in SQL:

select EmployeeID, count(DateKey) from (
select EmployeeID, f.DateKey
from EmployeeTimesheet f
join Date d on d.DateKey = f.DateKey
and f.DateKey between 20190101 and 20190131
and IsWeekday = 1
group by EmployeeID, f.DateKey
having sum(MinutesWorked) > 60
) a
group by EmployeeID

Just not sure if there's a way to do the same thing in DAX.

[v-cherch-msft · Accepted solution]

Hi @cmorrow

Please check WorkCount measure in attached file.Link the two tables with DateKey column.

Regards,

Cherie

[cmorrow]

Cherie - 

You are my hero.  That works perfectly.  Thank you!