Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |