add a calculated column from multiple values - find the first value?

[STimon]

Hi all,

I'm trying to build a table with uniques VAT numbers and Customer Names from different sources, to consolidate some reports, and I need that in the end, the user may filter the reports by the account name and not the VAT identifier (since the users known the name of their accounts but not the VAT ids).

I have 4 tables with VAT numbers, and I already have built a new table with the union of all VAT numbers -only unique values and not blank values-. I've linked this new table to the other 4.

But I can't find the way to add a columnt that gives me the customer name. In the original tables there are multiple values, so related or lookup value doesn't work.

Any idea to add a calculated column that finds the customer name in the new table?

Thanks!!

Silvia

[v-piga-msft]

Hi @STimon ,

More details will help us understand your scenario better.

It seems that you have multiple tables, which table do you want to create the calculated colum?

If it is convenient, could you share some sample data and your desired output so that we could understand your scenario better and get the solution.

Best  Regards,

Cherry

[STimon]

Hi, thanks for your answer, I'll try to explain with these tables:

I have Table A, with some Account names, identifier and movements:

TABLE A	Movements Source 1
Account Name	VAT ID	Movement
Example1	A1111111	250
Example1	A1111111	300
Example2	A1111112	150
Example3	A1111113	450

I have Table B, in which some identifiers are the same than Table A, but the account names are diferent, and other are new:

Table B	Movements Source 2
Account Name	VAT ID	Movement
1Example	A1111111	675
Ex2	A1111112	450
Ex2	A1111112	450
Exxxx4	A1111114	750

And I have Table C, with most of the account identifiers (but not all) and different (or not) account names:

TABLE C	Accounts Source 3
Account name	VAT ID
Account 1	A1111111
Account 2	A1111112
Account 3	A1111113

In new table D, I've got all the unique identifiers from the other 3 tables. And I have relationed VAT ID column of Table D with A, B & C.

NEW TABLE D
VAT ID	Account Name
A1111111
A1111112
A1111113
A1111114

I need to add a column that finds the Account name: if the identifier exists in Table C I'll choose the Account name from table C, if not exists, I'll search the name in table A and then table C.  The order of search shouldn't be a problem, because I could add 3 columns, "Name in table C", "Name in table A" and "Name in table B" and then a New Column with the final name.

But I can't work the DAX formule to get the account name from any of the tables.

Best Regards,

Silvia