Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BogdanL
Frequent Visitor

Oracle Direct Query text filter causes visual crash

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:

  • Power BI Desktop version: 2.138.1004.0 64-bit (November 2024)
  • Oracle Driver: Oracle Client for Microsoft Tools (OCMT) x64
  • Oracle version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64-bit Production

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!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

2 REPLIES 2
lbendlin
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.