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

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?

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

Sorry for late reply. It coming out blank(no data, no error)

 

Thanks

 

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

    This is a bit of a head scratcher. 
    Can you add a test for labour hours into the code as follows and then return _rowHours and let me know. The basic premise is that we added the operator name to each row in the table and now we are doing a simple sum for each operator for each workdate. 

    Operator Total Hours2 = 
    var _rowHours =
    codeTable[labourhrs]
    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
    I greatly appriciate your time
     
    No data(blank)and no error
     
    Operator Total HRs2 =
    VAR _RowHours=
    'LEM FACT with query'[LABORHRS]
    VAR _OperatorName=
    'LEM FACT with query'[OparatorName]
    VAR _workdate=
    'LEM FACT with query'[WORKDATE]
    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]=_workdate)
    )
    Return
    _RowHours
     
     
  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    3 years ago

    Well now we are getting somewhere.
    Going back to the beginning...

    We started with...

    If you created a calculated column at this point that was 

    RowHours = codeTable[labourhrs]

    It should just pull the labour hours over for all rows except the equipment rows. 

    Can you double check that this happens in your data?

    Assuming that works correctly, we added the Operator Name to each row.

    operatorName = 
    var _nameLookup =
    LOOKUPVALUE(codeTable[name],codeTable[Laborcode],codeTable[Equipmentlaborcode])
    Return
    IF(
        OR(ISBLANK(_nameLookup), _nameLookup=""),
        [name],
        _nameLookup
    )

    All this is doing looking in the labourcode column for the Equipmentlabourcode and returning whatever the name is for that row. The Equipmentlabourcode and labourcode need to be in the same format for this to work. If this worked correctly the table that results should look like...

    Can you verify that this is the result you get?

    Let me know how these work and we will go from there.

     

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

    Its not pulling Operator name for employee_name.

     

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

    I think I see it...

    Replace the code as follows (in bold)...

    operatorName = 
    var _nameLookup =
    LOOKUPVALUE(codeTable[name],codeTable[Laborcode],codeTable[Equipmentlaborcode])
    Return
    IF(
        OR(_nameLookup=",", _nameLookup=""),
        [name],
        _nameLookup
    )