Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
pls try this
Column =
COALESCE(
CONVERT(
CALCULATE(MAX('TableA'[Key]), FILTER(ALL(TableA),TableA[Name]='TableB'[Name]&&TableA[ID]='TableB'[D])),STRING),
[Name])
seems to be working brilliantly. thank you for your help
pls try this
Column =
COALESCE(
CONVERT(
CALCULATE(MAX('TableA'[Key]), FILTER(ALL(TableA),TableA[Name]='TableB'[Name]&&TableA[ID]='TableB'[D])),STRING),
[Name])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |