Cannot calculate sum of binary Matrix

[patrick_freeman]

I am attempting to create a matrix that shows the number of employees clocked in at each point in the day. So far, I have been able to create one that shows who is clocked in at each point based on their clock in and clockout time. However, when attempting to sum the binary values that indicate whether an employee is present or not, I am left with a sum = 1. See attached for the visual and DAX expressions used. 

[v-yanjiang-msft · Accepted solution]

Hi @patrick_freeman ,

According to your description, here's my solution.

1.Create a measure, and put the measure in the visual Values.

Column Values = 
VAR StartTime=CALCULATE(MIN('Labor'[Clock In]),ALL('Time'))
VAR EndTime=CALCULATE(MAX('Labor'[Clock Out]),ALL('Time'))
VAR Dur=MIN('Time'[Time])>=StartTime&&MAX('Time'[Time])<=EndTime
VAR _Count=COUNTROWS(FILTER('Labor','Labor'[Clock In]<=MAX('Time'[Time])&&'Labor'[Clock Out]>=MAX('Time'[Time])))
RETURN
IF(Dur,IF(ISINSCOPE(Labor[Name]),1,_Count))

2.Turn on the Column Values backgroud color and font color, both are set to Gradient format.

Get the correct result.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[tamerj1]

Hi @patrick_freeman @patrickfreeman 
Would you please share a screenshot of your report?

[amitchandak]

@patrick_freeman , Try a measure like

if(countrows(filter(labor, labor([clock in time]) <= max('Time'[Time]) && labor([clock out time]) >= min('Time'[Time]))) >=1, 1,blank())

[patrickfreeman]

Is there a way to make my time column a variable? I am still getting used to DAX and struggling to frame this data in a non-excel way

[patrickfreeman]

I appreciate the response. I think I may not have been clear in my first post. Right now, that is the result I'm getting (sum = 1). I am looking to see if I can assign a value to each employee so if I were to take the sum at a certain part of the day the sum = # of employees clocked in (i.e. = 3)