Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Community,
I am encountering a critical error when trying to search using a text filter in a Direct Query report connected to an Oracle 11g database.
My issue appears similar to the one described in the thread: Searching a slicer in my Direct Query report blows... - Microsoft Fabric Community.
Upon investigation, I found that the Power BI Oracle connector generates an incorrect SQL query, causing the error. Below are the details of my environment:
The error I receive is: ORA-00904: "t4"."CIG": invalid identifier.
This error arises from the UPPER("t4"."CIG") condition in the SQL WHERE clause generated by Power BI, which is deeply nested in subqueries and leads to runtime issues.
DAX Generated by Power BI:
VAR __DS0FilterTable = FILTER(
KEEPFILTERS(VALUES('V_G4_LOTTI'[CIG])),
SEARCH("ZY", 'V_G4_LOTTI'[CIG], 1, 0) >= 1
)
VAR __DS0Core = CALCULATETABLE(
VALUES('V_G4_LOTTI'[CIG]),
KEEPFILTERS(__DS0FilterTable)
)
VAR __DS0PrimaryWindowed = TOPN(101, __DS0Core, 'V_G4_LOTTI'[CIG], 1)
EVALUATE __DS0PrimaryWindowed
ORDER BY 'V_G4_LOTTI'[CIG]
SQL Generated by Power BI Connector:
SELECT *
FROM (
SELECT
"t4"."CIG"
FROM
((
SELECT
"_"."ID_INTERNO_LOTTO" AS "ID_INTERNO_LOTTO",
"_"."CIG" AS "CIG",
"_"."OGGETTO_LOTTO" AS "OGGETTO_LOTTO"
FROM "GGAP3_PREP"."V_G4_LOTTI" "_"
)) "t4"
WHERE
(
(
SELECT INSTR(Op2, Op1, Op3)
FROM (
SELECT UPPER('ZY') AS Op1,
UPPER("t4"."CIG") AS Op2,
1 AS Op3
FROM DUAL
) AuxTable
) >= 1
)
GROUP BY "t4"."CIG"
ORDER BY "t4"."CIG" ASC
) WHERE ROWNUM <= 101
The invalid SQL seems to stem from the reference to UPPER("t4"."CIG") in the WHERE clause. Since "t4"."CIG" is nested within the subqueries, Oracle does not recognize it at runtime.
I would greatly appreciate any insights or solutions the community can offer to resolve this issue.
Thank you in advance for your help!
Solved! Go to Solution.
If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .
If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .
Thanks!
Following your instructions, i've opened this issue: Oracle Direct Query text filter causes visual cras... - Microsoft Fabric Community
User | Count |
---|---|
73 | |
69 | |
36 | |
25 | |
22 |
User | Count |
---|---|
97 | |
92 | |
54 | |
44 | |
40 |