cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

 

Filter Starts by.PNG

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...

 



 

Status: New
Comments
Microsoft

Hi @PedroReis ,

 

"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

Regular Visitor

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:

 

Sample filter.png

Regular Visitor

bump

Idea Statuses