cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jhartranft60 Regular Visitor
Regular Visitor

Trying to look up value if the value appears within a PATH

Hello,

 

I have 2 tables.  The first has employee information, including their hierarchy (ID1|ID2|ID3|ID4).  The second table has an ID column with each of our 5 regions' ID and the name of the region.  I'm trying to categorize each employee into a region.  

 

Example:

Let's say Jill has PATH (B1045|B7645|B3428|B8954)

 

IDRegion
B8765Central
B7645North
B9743South
B2902East
B1327West

 

Since ID "B7645" is in Jill's hierarchy and listed in the table for Region "North", I'd like the formula to return "North", but not sure how to search the path for an ID that's also in the Region Table. (FYI - while it's possible a hierarchy will not contain any of the ID's in the Region table, it will never contain more than one).

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
v-jayw-msft Senior Member
Senior Member

Re: Trying to look up value if the value appears within a PATH

Hi @jhartranft60 ,

 

According to your sample data, you can use LOOKUPVALUE() funcation, please refer to below measure and see if the result achieve your expectation:

Measure = 
var d1 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),1,5))
var d2 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),7,5))
var d3 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),13,5))
var d4 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),19,5))
return
IF(d1<>BLANK(),d1,IF(d2<>BLANK(),d2,IF(d3<>BLANK(),d3,IF(d4<>BLANK(),d4,"NA"))))

Result would be shown as below:

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
v-jayw-msft Senior Member
Senior Member

Re: Trying to look up value if the value appears within a PATH

Hi @jhartranft60 ,

 

According to your sample data, you can use LOOKUPVALUE() funcation, please refer to below measure and see if the result achieve your expectation:

Measure = 
var d1 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),1,5))
var d2 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),7,5))
var d3 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),13,5))
var d4 = LOOKUPVALUE('Table'[Region],'Table'[ID],MID(MAX(employee[PATH ]),19,5))
return
IF(d1<>BLANK(),d1,IF(d2<>BLANK(),d2,IF(d3<>BLANK(),d3,IF(d4<>BLANK(),d4,"NA"))))

Result would be shown as below:

1.PNG

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)