DAX in Direct Query

[tsarun1992]

Hi All,

I have a complex data model & it is structured in a way to pull out realtime data using Direct Query mode.

With this setup, I am unable to use some DAX like ConcatenateX, LOOKUPVALUE which is highly needed for me to solve a need.

When I use these, it says the function is complex and asks me to use a simpler ones or it says as below

Are there any alternate DAX to fulfil the function of these DAX?

[v-tangjie-msft · Accepted solution]

Hi @tsarun1992 ,

In direct query connection mode many dax functions will be limited. So you can clean the data in power query.

Please check out the demo in the attachment. It's a solution in Power Query (the Custom column).

(let currentCustomer = [CustomerName] in Table.SelectRows(Table1, each [CustomerName] = currentCustomer)){0}[CustomerID]

You can refer to the following documents that may be helpful to you:

How to CONCATENATE in Power Query ?- with Examples (gyankosh.net)

Power BI Concatenate Stings with Power Query M Language (mssqltips.com)

LOOKUPVALUE in PowerQuery - Power BI Ninjas

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.