Starts with Filter does not work in Direct Query Mode in Power BI, but it works in Excel same Cube

[Anonymous]

When using a "Starts with" filter like Column A starts by "I" in Power BI, it fails.

Context:

• Connected to SSAS 1400 tabular cube in Direct Query mode.
• Source data: Oracle 11g database. Model contains tables generated by custom SQL Query.
• Oracle Instant 64 12cR1 v12.1.0.2 and Oracle Data Access Components 12cR1 v12.1.0.2 are installed

When connecting to the same SSAS cube in Excel and generating the same report, it works fine.

The reason it is failing in PBI is because The error is "ORA-00904: "c75": invalid identifier", meaning that Oracle is not able to recognized the c75 alias inside the subquery.

When evaluating the SQL Query that was generated, we see a Where condition placed in the end. I compared the same WHERE condition in Excel, and it's different. The Power BI one fails, the Excel one works.

Excel:

WHERE  "c75" = 'IAS'

Power BI:

WHERE ( (SELECT INSTR(Op2, Op1, Op3) FROM (SELECT UPPER('I') AS Op1, UPPER("c75") AS Op2, 1 AS Op3 FROM DUAL) AuxTable) = 1)

Here is the full query that was produced by PBI:

SELECT "c75", SUM ( "c78" ) "a0"

FROM (

SELECT "t5"."BOOK_CODE" "c75","t5"."END_BASE_AMT_CONS" "c78"

FROM (

(select "ASOF_DT",

    "FISCAL_YEAR",

    "....more fields..."

CONCAT("NX_PCC_FINREP","ASOF_DT") "K_TD_NX_PCC_TBL_DTEFF",

CONCAT("NX_PCC_COP","ASOF_DT") "K_TD_NX_PCC_TBL_DTEFF_CSB",

CONCAT("NX_TIERS_COP_CD","ASOF_DT") "K_TD_NX_COPERNIC_CD_DTEFF_1"

from "ESIOA"."PS_NX_FINREP_TBL" "$Table")

) "t5"

) "t5"

WHERE (
(SELECT INSTR(Op2, Op1, Op3) FROM (SELECT UPPER('I') AS Op1, UPPER("c75") AS Op2, 1 AS Op3 FROM DUAL) AuxTable)
= 1)

GROUP BY "c75"

Others have already reported similar problems, still unsolved:

https://community.powerbi.com/t5/Issues/Query-Error-on-Search-Field-of-Slicer-when-Using-Direct-Quer...

https://community.powerbi.com/t5/Issues/DAX-bug-Working-with-Direct-Query-with-Oracle-DB/idi-p/13789...

[v-yulgu-msft]

Hi @Anonymous ,

"Start with" filter is not available in my Power BI desktop (2.68.5432.841 64-bit). Which version did you use? How did you apply such a filter? Within visual or page level filter?

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft !

The filter can be applied at any level, Visual level, Page level, Report level. Here is a sample of how to find it:

[Anonymous]

bump