Having trouble with Lookup Values where duplicate values

[Shakeerm]

I have Two Tables, 

1. HR Performance Analysis Report

Which otlines the Tasks for each User and for each Job. And the Tasks are obviously duplicated in this Table

2. Then i have the Exceptions Table

Which Outlines From where the Task is coming in i.e. Booking Shipment etc from within the System and to which Mode it belongs to (as Highlighted below). 

In this some Tasks are somehwhat the same (duplicated) in all the Modes. 

Now, what i'm trying achieve is to get the Mode from the Exceptions Repot to HR Performance Report by using a Lookup Value DAX. However due to duplicate Tasks in the Exceptions Table i'm running to an Error on the HR Performance Report and the Lookup function doesn't work. 

Much obliged to those genuises who can help me to find a way around this crux. 

[az38]

Hi @Shakeerm 

try to use aggregation technique, it should work faster then LOOKUPVALUE. I m not sure what exactly do you nedd, but it could look like

Column = 
MAXX(
  FILTER(ALL('Exceptions Table'),
  'Exceptions Table'[User] = EARLIER('Table1'[User]) && 'Exceptions Table'[Job] = EARLIER('Table1'[Job])
  ),
  'Exceptions Table'[Task]
)

[Shakeerm]

Hi @az38 

I'm trying to look up the Mode in Exception List Table with the Tasks in the HR Performance Table. I want to show how many Tasks for each Mode is there in a Visual. 

[az38]

@Shakeerm 

if i understand you correct you need smth similar with

Measure = 
CALCULATE(DISTINCTCOUNT('Exception List Table'[Task]), FILTER(ALL('Exception List Table'), 'Exception List Table'[Mode] = MAX(Table1[Mode])) )