Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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's avatar
    v-qiuyu-msft
    Icon for Community Support rankCommunity 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's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion

    Anonymous

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

    haven't tested it but could work?