Forum Discussion

patrick_freeman's avatar
patrick_freeman
New Member
4 years ago
Solved

Cannot calculate sum of binary Matrix

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's avatar
    v-yanjiang-msft
    4 years ago

    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.

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      patrickfreeman
      New Member
      4 years ago

      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)

    • patrickfreeman's avatar
      patrickfreeman
      New Member
      4 years ago

      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

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.