Forum Discussion

User19's avatar
User19
Frequent Visitor
9 years ago

dynamic exchange rate

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!

Need Help

5 Replies

  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago
    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's avatar
      User19
      Frequent Visitor
      9 years ago

      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.

  • Baskar's avatar
    Baskar
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      User19
      Frequent Visitor
      9 years ago

      AnonymousThanks. However import is not an option.