Forum Discussion
Solved
Combining First Non Blank with Filter
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!
pls try this
Column = COALESCE( CONVERT( CALCULATE(MAX('TableA'[Key]), FILTER(ALL(TableA),TableA[Name]='TableB'[Name]&&TableA[ID]='TableB'[D])),STRING), [Name])
