Lookup Help please

[Anonymous]

Hello,

I have two data tables, each with Account Names and Revenue numbers. Some of the accounts are "special" and are called "AMSI" or "CMA", so I have a lookup table with these accounts with the Account Name and "AMSI" or "CMA". There are only a few account names on this list, but I need to create visualisations with "AMSI", "CMA" and "Other". i.e. if the account in the two data tables isn't in the lookup table, I get "Other" in the visualisation. 

I have been able to do this by adding a column in each of the tables;

AMSI = if(isblank(LOOKUPVALUE('NEU AMSI'[Account Type],'NEU AMSI'[Account Name],'Power BI Sold'[Account Name])),"Other",LOOKUPVALUE('NEU AMSI'[Account Type],'NEU AMSI'[Account Name],'Power BI Sold'[Account Name]))

This works, but unless I am mistaken, I will need to add this column to all tables that I want to segment this way, and there will be many more than two.

Is there another way to do this so that I don't have to add columns?

I hope this makes sense, and thank you for any help.

[v-juanli-msft · Accepted solution]

Hi

Create a table containing all Account names from all tables

Table =
GROUPBY ( UNION ( Sheet2, Sheet3, Sheet4 ), [ Account Names] )

Add a column to define if they are "AMSI", "CMA" or"Other".

Column 2 =
IF (
    ISBLANK ( RELATED ( Sheet1[Account Name] ) ),
    "others",
    RELATED ( Sheet1[Account Name] )
)

In table visual

 

Best Regards

Maggie

[Anonymous]

Maggie,

Thank you so much, that put me on the right track, and I now have a working report exactly the way I wanted it.

Brilliant

Andrew