DirectQuery Lookupvalue function

[DCrowther]

Hi

I have recently used DirectQuery for the first time to pull back large data sets, however i still need to do lookupvalue queries with other tables so i can relate the data to other categories, it appears LOOKUPVALUE query formula is not compatable with in the DirectQuery Table mode. 

Does any know how you can create new lookup columns from another table in a DirectQuery table?

Thanks

Dan

[v-shex-msft]

Hi @DCrowther ,

Based on my test, direct query mode not allow you to use a few calculation functions in calculated column. You can consider to use measure to lookup other tables fields.

DAX formula compatibility in DirectQuery mode

Regards,

Xiaoxin Sheng

[DCrowther]

Hi Xiaoxin

Thankyou for your reply, i have begun to try to lookup this issue and saw about creating the lookupvalue as a Measure, however even though the tables are related (i have linked the related fields in the 'Model' section) when i come to write the LOOKUPVALUE formula in the Measure, the last part of the formula does not recognise/match the field with with the current table i am in (i.e. its not recognising the search_value) in fact it is only picking the other measures that are in the table. Do i have to some how convert the search_value into a measure?

It looks like it works slightly different than from the LOOKUPVALUE function from imported data to LOOKUPVALUE with DirectQuery

Any ideas?

Regards

Dan

[v-shex-msft]

HI @DCrowther ,

You can use measure as search value but you can't use it as search field, lookupvalue function not support to search on measure.

You can use calculate function with filter function to instead:

Measure =
CALCULATE (
    MAX ( Table[Amount] ),
    FILTER ( ALLSELECTED ( Table ), [Mesure2] = 1 )
)

Regards,

Xiaoxin Sheng