PowerPivot: Show a category in a table with One to many relationship

[bright_mind]

I have Table A and Table B

Table A has the unique personnel ID
Table B has the the field "Category" where personnel ID can have multiple categories selected, e.g.,

Table A:
Key Name
1 John
2 Jacob
3 Jane

Table B:
Key Category Sub Category
1 Personnel Type Management
1 Job Level Director
1 Hire Type Internal
2 Personnel Type Clerk
2 Job Level Career
2 Hire Type External
3 Personnel Type Management
3 Hire Type External


What formula should I enter in a calculated field in PowerPivot if I would like to show in Table A the value for "Job Level"? I understand that Related function won't work?

 

[Matt Allington]

Why do you want this in Table A? You should avoid this. What are you trying to acheive? How about create a pivot, place TableA[name] on rows, then write this measure and place on values.

Job Level Measure = calculate(if(hasonevalue(tableB[sub category]),values(tableB[sub category])),TableB[Category]="Job level")

this first filters table B so only job level is unfiltered, then returns the single value of sub category (as long as there is a single value)

 

[bright_mind]

Thank you! This measure is exactly what I need! I'm just not familiar it exists.