DAX sumx and if

[vxmine]

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.

[mwegener · Accepted 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.

[vxmine]

As a side note, how did you know the ,1 is an argument? Is it common for DAX? I'm new to Power BI. 

[mwegener]

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

https://dax.guide/lookupvalue/

[vxmine]

That worked, thanks!