Lookup in Diff Table if No Match then Default Value

[Anonymous]

I have two different tables that have a relationship built. Many:1 on the Country_Item column (Sales:Items).

I want to do a calculated column that is a lookup that says if there is a match on the field between the two columns, then return the matching price from Items. If there is no match in the Items table, then return the price for US. 

Table 1: Items

Headers:

Country_Item       ListPrice

us_P123                1.15

ca_P123                2.00

Table 2: Sales

Headers:

SalesOrder    Country_Item       ListPrice

MX12355       mx_P123              (us_P123 price is what I want here)

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

I created two tables with the dummy data below:

We can create a calculated column below in Sales table:

ListPrice = IF(ISBLANK(RELATED(Items[ListPrice])),CALCULATE(MAX('Items'[ListPrice]),FILTER('Items','Items'[Country_Item]="us_P123")),RELATED(Items[ListPrice]))

Best Regards,
Qiuyun Yu

[vanessafvg]

@Anonymous

measure =
IF (
ISBLANK ( RELATED ( items[listprice] ) ),
sales[listprice],
items[listprice]
)

haven't tested it but could work?