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

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

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

        Thanks. That allows me to know that there are no errors in the data. 

        When you use 

        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

        What data values or errors are returned? Could you grab a screen shot of the results?