Look up laborcode in another row in same table in Dax

[ABC11]

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

Workdate	Laborcode	name	laborhrs	equipment	Equipment hrs	Equipmentlaborcode	vendequipmentunit	Operator_name	Oprator total hrs
12/1/2022	30017091	Landry	0.5
12/1/2022	30004386	LETENDRE	3
12/1/2022	30012147	Penkov	0.5
12/1/2022	30004376	ROY	2.5
12/1/2022	30004393	Vergunov	3
12/1/2022	30004376	ROY	5.5
12/1/2022	30004393	Vergunov	5
12/1/2022	30004393	Vergunov	2
12/1/2022				160 TON	4.5	30004393	160-1-4433	Vergunov	10
12/1/2022				160 TON	7	30004376	160-1-4433	ROY	12.5
12/1/2022	30004376	ROY	2
12/1/2022	30004376	ROY	2.5
12/1/2022	30017091	Landry	2
12/1/2022	30017091	Landry	1
12/2/2022	30004370	BANARES	0.5
12/2/2022	30017091	Landry	0.5
12/2/2022	30004370	BANARES	1
12/2/2022				100 TON	2	30004370	100-1-4617	BANARES	10
12/2/2022	30004370	BANARES	7.5
12/2/2022	30004370	BANARES	1
12/2/2022				100 TON	6	30017091	100-1-4475	Landry	11
12/2/2022	30017091	Landry	7.5
12/2/2022	30017091	Landry	2
12/2/2022	30017091	Landry	1

Thanks lots

[jgeddes]

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

[ABC11]

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]

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]

 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]

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]

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]

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]

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]

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]

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

Thanks

[jgeddes]

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]

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]

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]

Its not pulling Operator name for employee_name.

[jgeddes]

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
)

[jgeddes]

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]

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

[jgeddes · Accepted solution]

You can change Operator Total Hrs2 to Return _calc now.

[ABC11]

Perfect!

Thanks lots for your help

[ABC11]

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