measure based on two tables

[rosscortb]

Hello,

I would attach a sample but it's massive file so just try and explain what I'm trying to do.

I have an employee table.

ID

Name
Position Group( Director, Job Leader, Manager, Part Leader, Senior Ops Manager, Site manager, Task Supervisor, Team Leader)

Org unit number

I have an org table

Org unit number

Org Level(Level 1,4,5,6,8,9,10,11)

I am trying to bring through org level against the employee. I know I can create a relationship between org unit number.

But, it this relates to the position group. So, not all employees have a position group or Org Level.

For example, if you are a Team Leader, you're Org Level will be 5.

So, I think i need a measure instead?

Hope that makes sense.

Regards

Ross

[Greg_Deckler · Accepted solution]

@rosscortb You can use LOOKUPVALUE in a measure or a calculated column. Or a MAXX coupled with a FILTER.

[rosscortb]

@Greg_Deckler 

Thanks Greg, so what can I add this to this that will make it only return values if there is a value in the position group column.  = 'Spans & Layers'[Position Group]

Org Level = LOOKUPVALUE('Org Master Data'[Org. Level_1],'Org Master Data'[ObjectID],'Spans & Layers'[Org.unit])