The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Good day,
I am trying to create a caluclated column, which pulls in a value "conversion rate" from a currency conversion rate table, based on a specified date, and the currency code (Cdn, AUD etc). There is no link between the two tables, as they do not share any unique identifiers.
In order for the correct conversion rate to be pulled, the "Close Date" from the original Opportunity table has to fall within a range "Start Date" - "Next Start Date" in the currency table. In addition, the Currency ISO codes between the two tables must also match. Here is the syntax that I tried, but it returns an error: A table of multiple values was supplied where a single value was expected.
Being new to Power BI, I am not sure if this is even the correct approach? Any advice would be appreciated!
//
Conversion Rate = if(and('Opportunity Product_ACVe'[Close Date]>=related('Dated Conversion Rate'[StartDate]),'Opportunity Product_ACVe'[Close Date]<=related('Dated Conversion Rate'[NextStartDate])),(LOOKUPVALUE('Dated Conversion Rate'[ConversionRate],'Dated Conversion Rate'[IsoCode],'Opportunity Product_ACVe'[CurrencyIsoCode])),0)
//
Hi @Anonymous,
Based on error message, it seems like you formula can find out multiple records based on above conditions.
Current calculated column and measures not support stored multiple records, I think you need to modify your formula to cut down the result amount or use CONCATENATE function to merge them to string.
Can you please share some sample data to test? it will be help for troubleshooting your scenario.
Regards,
Xiaoxin Sheng
Good day
I am trying to pull the correct currency exchange rate given a specific date that falls under a date range in the destination table, and a specific currency code. The two tables are not related. This is the syntax I tried, but it returns an error: A table of multiple values was supplied where a single value was expected.
Any help would be greatly appreciated!
Conversion Rate = if(and('Opportunity Product_ACVe'[Close Date]>=related('Dated Conversion Rate'[StartDate]),'Opportunity Product_ACVe'[Close Date]<=related('Dated Conversion Rate'[NextStartDate])),(LOOKUPVALUE('Dated Conversion Rate'[ConversionRate],'Dated Conversion Rate'[IsoCode],'Opportunity Product_ACVe'[CurrencyIsoCode])),0)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
273 | |
129 | |
123 | |
101 | |
91 |