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.

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

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

        When I replace _calc with Operatorname then retuen Operatorname.

        When I replace _calc with Workdate tehn return Workdate.

        But I need total hrs.

        Thanks

        Rupal