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
Only this is not working. 
Operator Total HRs =
CALCULATE(
sum('LEM FACT with query'[LABORHRS]),
ALLEXCEPT('LEM FACT with query','LEM FACT with query'[EMPLOYEE_NAME],'LEM FACT with query'[WORKDATE]))
 
Operator Name formula is working
 
Thanks 
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