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.
Hello all,
I need a little help here. I'm trying to create a new measure that converts a contract value to USD (the project table tells me the currency type of the contract_value). The below measure works for all currencies except for USD:
Contract Value (USD) = sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID]))
The currency_exchange table has all the rates except USD (the ERP is a USD based system), so I'm getting no values in the measure. Below is my attempt to forces a USD calculation.
Contract Value (USD) = if(project_info[currency_id]<>'USD', sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID])), [project_info[contract_value]*1)
Any advice from you pros is greatly appreciated.
Solved! Go to Solution.
Hi @vxmine ,
try this
Contract Value (USD) = sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID], 1))
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @vxmine ,
try this
Contract Value (USD) = sumx(PROJECT_INFO, PROJECT_INFO[CONTRACT_VALUE]* LOOKUPVALUE(CURRENCY_EXCHANGE[SELL_RATE],CURRENCY_EXCHANGE[CURRENCY_ID], PROJECT_INFO[CURRENCY_ID], 1))
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
As a side note, how did you know the ,1 is an argument? Is it common for DAX? I'm new to Power BI.
You can find the parameter in the documentation.
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
That worked, thanks!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |