Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
When using a "Starts with" filter like Column A starts by "I" in Power BI, it fails.
Context:
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.