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 operator by name.

I did create column

Operator_name = if('LEM FACT with query'[EQUIPLABORCODE]='LEM FACT with query'[LABORCODE],'LEM FACT with query'[EMPLOYEE_NAME],"No Name")
But not working for me.
Also possible I need the oparator total hrs in last column as well for that day.
Last two column are output column
WorkdateLaborcodenamelaborhrsequipmentEquipment hrsEquipmentlaborcodevendequipmentunitOperator_nameOprator total hrs
12/1/202230017091Landry0.5      
12/1/202230004386LETENDRE3      
12/1/202230012147Penkov0.5      
12/1/202230004376ROY2.5      
12/1/202230004393Vergunov3      
12/1/202230004376ROY5.5      
12/1/202230004393Vergunov5      
12/1/202230004393Vergunov2      
12/1/2022   160 TON4.530004393160-1-4433Vergunov10
12/1/2022    160 TON730004376160-1-4433ROY12.5
12/1/202230004376ROY2      
12/1/202230004376ROY2.5      
12/1/202230017091Landry2      
12/1/202230017091Landry1      
12/2/202230004370BANARES0.5      
12/2/202230017091Landry0.5      
12/2/202230004370BANARES1      
12/2/2022   100 TON230004370100-1-4617BANARES10
12/2/202230004370BANARES7.5      
12/2/202230004370BANARES1      
12/2/2022   100 TON630017091100-1-4475Landry11
12/2/202230017091Landry7.5      
12/2/202230017091Landry2      
12/2/202230017091Landry1      
 
 
Thanks lots
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    You can change Operator Total Hrs2 to Return _calc now.

20 Replies

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

    You can add the following calculated columns...

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

    and 

    Operator Total Hours = 
    CALCULATE(
        SUM(codeTable[laborhrs]),
        ALLEXCEPT(codeTable, codeTable[operatorName], codeTable[Workdate])
    )
    • ABC11's avatar
      ABC11
      Icon for Resolver I rankResolver I
      3 years ago

      Thanks lot to Solution Sage,

      Operator name is working fine but Operator Total hours coming blank. Would you help me to figure out.

      Thanks again

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

      Now Operator Name is pulling. What is our next step? please

       

      • jgeddes's avatar
        jgeddes
        Icon for Super User rankSuper User
        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

    Sure.
    Share your formulas for both Operator Name and for Operator Total Hours.

    • 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.