Forum Discussion

ABC11's avatar
ABC11
Icon for Resolver I rankResolver I
3 years ago
Solved

Look up laborcode in another row in same table in Dax

Hi, I have laborcode(employee no), name and laborhrs in one row And euipment, equipmenthrs , equilaborcode in different row.  Laborcode and equilaborcode is same.  I would like to identify operato...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    You can change Operator Total Hrs2 to Return _calc now.

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
3 years ago

I just needed to see what you named the Operator Name column.
The name column that is referenced in the ALLEXCEPT statement needs to be the "Operator Name" column, not the original Employee Name column.

ABC11's avatar
ABC11
Icon for Resolver I rankResolver I
3 years ago
 When I change to OpratorName column still not working
Operator Total HRs =
CALCULATE(
sum('LEM FACT with query'[LABORHRS]),
ALLEXCEPT('LEM FACT with query','LEM FACT with query'[OparatorName],'LEM FACT with query'[WORKDATE]))
 
OparatorName =
var _nameLookUp=
LOOKUPVALUE('LEM FACT with query'[EMPLOYEE_NAME],'LEM FACT with query'[LABORCODE],'LEM FACT with query'[EQUIPLABORCODE])
Return
if(
or(ISBLANK(_nameLookUp), _nameLookUp=""),
'LEM FACT with query'[EMPLOYEE_NAME],
_nameLookUp
)
Please,
Thanks for your help
  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    3 years ago

    Ok, let us change the Operator Total Hours calculated column to this...

    Operator Total Hours2 = 
    var _operatorName =
    codeTable[operatorName]
    var _workDate =
    codeTable[Workdate]
    var _calc =
    CALCULATE(
        SUM(codeTable[laborhrs]),
        FILTER(ALL(codeTable), codeTable[operatorName] = _operatorName && codeTable[Workdate] = _workDate)
    )
    Return
    _calc

     

    • ABC11's avatar
      ABC11
      Icon for Resolver I rankResolver I
      3 years ago
      Sorry- Didn't work
       
      Operator Total HRs2 =
      VAR _OperatorName=
      'LEM FACT with query'[OparatorName]
      VAR _workdate=
      'LEM FACT with query'[WORKDATE].[Date]
      VAR _calc =
      Calculate(
      sum('LEM FACT with query'[LABORHRS]),
      FILTER(ALL('LEM FACT with query'),'LEM FACT with query'[OparatorName]=_OperatorName && 'LEM FACT with query'[WORKDATE].[Date]=_workdate)
      )
      Return
      _calc
       
      Please, look it again for me
      Thanks
      • jgeddes's avatar
        jgeddes
        Icon for Super User rankSuper User
        3 years ago

        Ok, replace the "_calc" with _OperatorName and see if the names are returned.

        Then replace _calc with _workdate and see if the workdates are returned.

        Let me know how that works