Combining First Non Blank with Filter

[shuhn1229]

Hi all,

I have two tables, lets call them A and B. Table A is a key column and is used to map naming conventions to B when a user uses an inappropriate name. However, this mapping can be different depending on the ID column in table B. In essence I need to create a calculated column that Filters the ID of Table A by Table B, checks if the name column of the record is in table B, and if the Name in the given row is there, show the value in table A. If not, go with the value of name column in table B. Representation below with calculated column in the far right of table b:

table A

ID	Name	Key
Apples	Q73	2
Apples	Q74	3
Oranges	R75	6
Pineapples	Q73	1
Pineapples	Q74	2
Pineapples	Q75	3

Table B

ID	Name	Value
Apples	Q50	Q50
Apples	Q51	Q51
Apples	Q52	Q52
Apples	Q73	2
Apples	Q74	3
Pineapples	Q20	Q20
Pineapples	Q21	Q21
Pineapples	Q73	1
Pineapples	Q74	2
Pineapples	Q75	3

Thank you!

[shuhn1229]

seems to be working brilliantly. thank you for your help

[Ahmedx · Accepted solution]

pls try this

Column = 

 COALESCE(
     CONVERT(
         CALCULATE(MAX('TableA'[Key]), FILTER(ALL(TableA),TableA[Name]='TableB'[Name]&&TableA[ID]='TableB'[D])),STRING),
     [Name])