ODBC Query: select a value from another table

[JeroenR]

Hi all,

I have the following query created with an ODBC connection:

"select id, date_from, date_to, partition_0#(lf)from ""database"".""database_table"" #(lf)where partition_0 = (select max(partition_0) from ""database"".""database_table"") "

What this query does is take every row where the partition_0 date is the latest. This query does work, but when I push this to the Power BI Web the first incremential refresh takes 2 hours to fix this and then gives an TimeOut error. I think the main reason of this is the "select max" query. There are about 65 million rows in this table, so this takes a lot of time and can be done much faster with an value instead of an query.

So what my idea is, is to create an new query where I only take this last partition_0 value with a LIMIT to 1. Then I want to select the outcome of this query and paste it in the query above. I already searched on this forum but couldn't really find a good solution for this. I also don't know if it is possible at all to paste an value from another query outcome in another query. 

Can somebody please answer me this question. Thank you in advance. 

[v-lionel-msft]

Hi @JeroenR ,

You can use DAX to calculate the values after getting the data through the ODBC connector.

Best regards,
Lionel Chen

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

[JeroenR]

Hi @v-lionel-msft ,

Thank you for your reply. Do you know what is faster? To filter this in the sql query and fetch 'only' 65 million rows of data, or to fetch 180 million rows of data and filter the partition_0 value field within DAX?