topic Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER? in DAX Commands and Tips

Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

Ricardo77 — Mon, 14 Sep 2020 13:00:49 GMT

Hello,

Im developing a PBI Report that is connected to Oracle using DirectQuery,
At one measure i use CROSSFILTER inside CALCULATE, which is itself inside SUMX

So the formula seems perfectly good, at least without any architectural or syntax error, but when refreshing the report with the new measure i always endup with this error:

The strange part is that this table (and this attribute in particular) aren't used in the report, so my question is if this could be

related to some sort of incompatibility between CROSSFILTER and DIRECTQUERY?

Regards

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

lbendlin — Mon, 14 Sep 2020 13:14:04 GMT

What does the "View Native Query" tell you about the query that Power BI sends to Oracle (or use Fiddler).

This particular attribute may be part of the extended table that Power BI generates. Check your data model again.

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

amitchandak — Mon, 14 Sep 2020 15:50:33 GMT

Ricardo77 , Invalid identifier is when column name is not found , any column name changed after you created data model in power bi

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

Ricardo77 — Tue, 15 Sep 2020 11:00:01 GMT

Hello lbendlin ,

Thanks for your time, 1st important comment: i'm in Live Query from PBI Desktop to SSAS, and in Direct Query from SSAS to Oracle.

Answering your comments:

What does the "View Native Query" tell you about the query that Power BI sends to Oracle (or use Fiddler).

-> If i've seen right, View Native Query is available through PowerQuery, which i'm not using.

-> In my organization they told me to use SQL Profiler to do a trace, so i've remembered also of DAX Studio (not tried fiddler, yet).

--> DAX Studio blows when running this query, got a message "Error Connecting to server: the connection either timed out or was lost" (i'm in direct query, so i connect to the SSAS Server)

--> I've searched for the table and column on a similar visual/DAX without the problematic CROSSFILTER, and neither the column and the table appear there. I don't know if could/should get the problematic query running (with CROSSFILTER), if the problematic column will explicitely appear there...

--> I've analysed the querie in SQL Profiler, with and without the CROSSFILTER, and i've noticed that with CROSSFILTER it gets way more complex, it seems to list every table and every column of the model (including the column that appears on the message).

I don't know if this problem is related to CROSSFILTER or to CROSSFILTER being used in DIRECTQUERY...

What should i look for on SQL Profile?

This particular attribute may be part of the extended table that Power BI generates. Check your data model again.

-> What do you mean by extended table? Are you basically referring to the query in DAX, or the the version in SQL?

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

Ricardo77 — Tue, 15 Sep 2020 09:54:47 GMT

Hello, i connect to the model using Tabular Editor and i can see the column with the exact name.

So i don't confirm your comment, unless you're referring to some kind of need of re-processing the model (?)

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

lbendlin — Tue, 15 Sep 2020 11:21:24 GMT

Extended Table is how the Power BI DAX engine actually works. Read up on it.

Your system architecture (Power BI -> Oracle -> SSAS ) sounds rather confusing to me.

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

Ricardo77 — Tue, 15 Sep 2020 12:40:34 GMT

Hello, i mean PBI Desktop <-[LiveQuery]- SSAS Tabular <-[DirectQuery]- ORACLE

When I search for extended table it appears concepts of Query Folding. As I've already got the SQL query, that's why i was asking what should i search for in the Query.

Regards

Re: Report connected to Oracle using DirectQuery - Error using CROSSFILTER?

lbendlin — Tue, 15 Sep 2020 13:00:34 GMT

CROSSFILTER(,,BOTH) produces something like a full outer join. Not surprising that your query blows up. Can you rewrite the measure in a different way that does not require the bidirectional filter?