Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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
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
@Anonymous
measure =
IF (
ISBLANK ( RELATED ( items[listprice] ) ),
sales[listprice],
items[listprice]
)
haven't tested it but could work?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |