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 t...
  • 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.

     

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

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