Forum Discussion
Lookup in Diff Table if No Match then Default Value
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)
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
2 Replies
- v-qiuyu-msft
Community Support
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
Community Champion
Anonymous
measure =
IF (
ISBLANK ( RELATED ( items[listprice] ) ),
sales[listprice],
items[listprice]
)haven't tested it but could work?