Lookupvalue not possible with Direct Query?

[rambler]

Hello

I think that this is a bit like the following:

https://community.powerbi.com/t5/Desktop/Lookupvalue-function-not-appearing-Direct-Query-mode/m-p/45...

but maybe not.

I have a Composite Model of DirectQuery and Import.

I would like to add a new column to a DirectQuery table with a LOOKUPVALUE calculated field. This will be added to a table visualisation in Report view.

Creating the calculated column in the DirecetQuery table results in an error message for the DAX stating that it is not possible in a DirectQuery table.

However, creating the calculated column in the imported table results in an error message for the DAX stating:

"A single value for column [ColumnX] in table [Table] cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." 

Is there a workaround? Previously the solution was to allow Unrestricted Measures in DirectQuery mode by way of a checkbox. However the checkbox has now been removed as it is implicit, as per this thread:

https://community.powerbi.com/t5/Desktop/What-Happened-To-quot-Allow-unrestricted-measures-in-Direct...

However, as stated above, LOOKUPVALUE is not possible with a DirectQuery table.

Thanks in advance!

[Anonymous]

I am trying to create a measure in DirectQuery mode .

customer_id = LOOKUPVALUE('public client'[customer_id],'public client'[id],[client_id])

I am trying to get customer_id from client table based on client_id in another table. 

I am getting this error 

"A single value for column [ColumnX] in table [Table] cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." 

Can this be achieved in Direct query mode , I am able to do the same in import mode?

[v-piga-msft]

Hi @rambler ,

Have you solved the problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[rambler]

Hi Cherry

@v-piga-msft

Many apologies for the late response. Other commmitments got in the way.

In order to post to some data, I would have to create a dummy dataset that would reproduce the error. Unfortunately, I am unable to do that at the moment.

If I do get the error message again, is it okay to resurrect this thread to update it? Or is starting a new one the preferred method?

Many thanks for your time, help, and patience

Rambler

[v-piga-msft · Accepted solution]

Hi @rambler ,

Yes, you're right. We cannot create calculated column with Lookupvalue function for the Direct Query mode table by my tests.

However, we could create the calculated column with Lookupvalue function for the import mode table.

In addition, we could create the measure with Lookupvalue function for the the Direct Query mode table.

---

However, creating the calculated column in the imported table results in an error message for the DAX stating:

"A single value for column [ColumnX] in table [Table] cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." 

---

For this error message, could you share the data sample and your formula which could reproduce the issue so that I could do a research on it?

Best  Regards,

Cherry