Forum Discussion
Custom ODBC Connector Property Unsearchable Error
Hi zua ,
This error happens because Power BI relies on ODBC metadata to decide whether columns can be filtered (searchable). If your ODBC driver (especially with DOUBLE or DOUBLE PRECISION columns) flags them as “unsearchable,” Power BI can’t push filters down in DirectQuery mode, which triggers this issue.
In some cases, as Pieterjan pointed out, the root cause is a mismatch or bug in how the ODBC driver reports column data types and searchability (for example, a conflict between SQL_DESC_TYPE_NAME and SQL_DESC_CONCISE_TYPE). This can happen even if the connector says the column should be searchable.
What you can do is to, Create a view in your database that CASTs the DOUBLE column to DECIMAL, and point Power BI to that view. This usually makes the column searchable and gets around the ODBC metadata problem.
If you manage the ODBC driver or connector, double-check how it exposes column metadata (especially those attributes Pieterjan mentioned). Fixing the metadata at the source is ideal if possible. Also, you can also try Import mode as a workaround, but I get that DirectQuery and query folding are important for you.
Hi zua and others who stumble upon this problem.
We had a similar issue and we found out that in our case this was due to a bug in our ODBC driver:
NativeQuery uses 'GetColumnAttributeW', attribute 'SQL_DESC_TYPE_NAME' to look up the datatype of the columns you query. The datatypes themselves are being fetched/queried using 'SQLGetTypeInfo' and the values being used for the lookup are in the ' SQL_DESC_CONCISE_TYPE' column. The issue in this case was a mismatch between both values and this caused this exact error.
Late answer, but hope this helps you and whoever else who stumbles upon this problem.
Kind regards,
Pieterjan Spoelders