dynamic exchange rate

[User19]

Hi,

I am looking to create a Calculated Column in Power BI Embedded, that will have values in currency user has selected as his base currency in appliacation.

So, I have created a UserCurrency which is measure

UserCurrency= LOOKUPVALUE(User[BaseCurrency],Users[UserId], USERNAME())

and calculated column like this which will fetch UnitValue from Exchange rate table based on User Currency.

ValueInBase = LOOKUPVALUE(ExchangeRate[UnitValue], ExchangeRate[Currency],[UserCurrency]) * Order[Value]

But it shows error that "Function 'LOOKUPVALUE' is not allowed as part of calculated column DAX expressions on DirectQuery models."

Please help.

Thanks!

[v-shex-msft]

Hi @User19,

In direct query mode there are some limitation to directly calculate the result value. Perhaps you can try to use import mode or calculate the result at query editor.(But I haven't found usename functions in power query)

Reference link:

DAX Formula Compatibility in DirectQuery Mode (SSAS 2016)

Regards,

Xiaoxin Sheng

[User19]

@v-shex-msftThanks. However import is not an option.

[Baskar]

Yes i agree with @dkay84_PowerBI his point. 

but am not sure all the DAX function is supporting in Direct Query mode.because have some restriction in Direct Query mode.

Look the microsoft documentation in Power BI Site.

[dkay84_PowerBI]

DirectQuery limits the extent of DAX functions you can use by default. Advanced users can choose to bypass this limitation by selecting File > Options and then Settings > Options > DirectQuery, then selecting the option "Allow unrestricted measures in DirectQuery mode". When that option is selected, any DAX expression that is valid for a measure can be used. Users must be aware, however, that some expressions that perform very well when the data is imported may result in very slow queries to the backend source when in DirectQuery mode

[User19]

Thanks for your reply. I do have this enabled.

Is there any other way? Bascially I want dynamic value (calcualted based on exchnage rate ) for user (USERNAME()) to be applied.