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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

DAX bug: Working with Direct Query with Oracle DB

I’m having serious troubles working with DAX there are several functions that aren’t working properly, I send you 4 different calculations with the same fields (the fields are in the same table) that Surprisingly Works good with Multiplication Arithmetic Operation but failds with Sum, the problem that I’ve found it’s that SQL statements generated by powerbi are not well generated, here I attach one example that Works * Arithmetic Operation and other sql that fails + Arithmetic operation.  I also found the same problem with divide and with - arithmetic operation.

 

I belive that the problems resides in the engine which is in charge of generation the sql statement, I really don't understand why generates diferent SQL statement when the only diferent thing is the arithmetic operation that Db have to do with the fields.

 

I’m working in DirectQuery againts a v11 Oracle database with ODAC121024_x64 driver.

 

Case 1: 

Result --> WORKS PROPERLY

Arithmetic operation --> Multiplication

DAX Formula in Powerbi--> Calc_test = '10x10_CONTEOPIEZAS_APRENDIZAJE'[PESO_APRENDIDO]*'10x10_CONTEOPIEZAS_APRENDIZAJE'[PESO_METRO_APRENDIDO]

 

SQL Statement that powerbi generates : Work properly

SELECT

SUM ( "t1"."Calc_test" )

"a0"

FROM

(

(

SELECT

"t1"."ID_LOTE" "ID_LOTE",

("t1"."PESO_APRENDIDO" * "t1"."PESO_METRO_APRENDIDO")

"Calc_test"

FROM

(

(select "$Table"."ID_LOTE" as "ID_LOTE",

    "$Table"."FECHA_HORA" as "FECHA_HORA",

    "$Table"."PIEZAS_PESADAS" as "PIEZAS_PESADAS",

    "$Table"."PIEZAS_AL_BOOKING" as "PIEZAS_AL_BOOKING",

    "$Table"."PIEZAS_AL_CARRO" as "PIEZAS_AL_CARRO",

    "$Table"."CODIGO_RECOGIDA" as "CODIGO_RECOGIDA",

    "$Table"."PESO_METRO_APRENDIDO" as "PESO_METRO_APRENDIDO",

    "$Table"."VELOCIDAD_APRENDIDA" as "VELOCIDAD_APRENDIDA",

    "$Table"."PESO_APRENDIDO" as "PESO_APRENDIDO",

    "$Table"."CORTE_APRENDIDO" as "CORTE_APRENDIDO",

    "$Table"."CONTEO_PESO" as "CONTEO_PESO",

    "$Table"."CONTEO_LONGITUD" as "CONTEO_LONGITUD",

    "$Table"."FECHA_ULTIMO_APRENDIZAJE" as "FECHA_ULTIMO_APRENDIZAJE"

from "POWERBI"."10x10_CONTEOPIEZAS_APRENDIZAJE" "$Table")

)

"t1"

)

"t1"

left outer join

(select "_"."ID_LOTE" as "ID_LOTE",

    "_"."FECHA_HORA" as "FECHA_HORA",

    ("_"."CODIGO" || '-') || "_"."VERSION_NUMBER" as "CODIGO_VERSION",

    "_"."VERSION_NUMBER" as "VERSION_NUMBER",

    "_"."CODIGO" as "CODIGO",

    "_"."STATUS" as "STATUS",

    "_"."ID_MAQUINA" as "ID_MAQUINA",

    "_"."TIEMPO_ESPERA" as "TIEMPO_ESPERA"

from "POWERBI"."10x10_LOTES" "_") "t3" on

(

"t1"."ID_LOTE" = "t3"."ID_LOTE"

)

)

WHERE

(

("t3"."FECHA_HORA" < TO_DATE('2017-03-04 00:00:00', 'YYYY-MM-DD HH24:Mi:SS'))

AND

("t3"."FECHA_HORA" >= TO_DATE('2017-02-02 00:00:00', 'YYYY-MM-DD HH24:Mi:SS'))

)

 

Case 2:

Result -->Return exception ORA-00904 invalid identifier

Arithmetic operation -->Sum

Dax Formula in powerbi --> Calc_test = '10x10_CONTEOPIEZAS_APRENDIZAJE'[PESO_APRENDIDO]+'10x10_CONTEOPIEZAS_APRENDIZAJE'[PESO_METRO_APRENDIDO]

 

SQL Statement that powerbi generates : in red the part of SQl code that throws the exception ORA-00904

SELECT

SUM ( "t1"."Calc_test" )

"a0"

FROM

(

(

SELECT

"t1"."ID_LOTE" "ID_LOTE",

(SELECT COALESCE(AuxTableOut.Op1 + AuxTableOut.Op2, AuxTableOut.Op1Casted, AuxTableOut.Op2Casted) FROM (Select Op1,

AuxTable.Op2

AS Op2,

AuxTable.Op1

AS Op1Casted,

AuxTable.Op2

AS Op2Casted FROM (SELECT "t1"."PESO_APRENDIDO" AS Op1, "t1"."PESO_METRO_APRENDIDO" AS Op2 FROM DUAL) AuxTable) AuxTableOut)

"Calc_test"

FROM

(

(select "$Table"."ID_LOTE" as "ID_LOTE",

    "$Table"."FECHA_HORA" as "FECHA_HORA",

    "$Table"."PIEZAS_PESADAS" as "PIEZAS_PESADAS",

    "$Table"."PIEZAS_AL_BOOKING" as "PIEZAS_AL_BOOKING",

    "$Table"."PIEZAS_AL_CARRO" as "PIEZAS_AL_CARRO",

    "$Table"."CODIGO_RECOGIDA" as "CODIGO_RECOGIDA",

    "$Table"."PESO_METRO_APRENDIDO" as "PESO_METRO_APRENDIDO",

    "$Table"."VELOCIDAD_APRENDIDA" as "VELOCIDAD_APRENDIDA",

    "$Table"."PESO_APRENDIDO" as "PESO_APRENDIDO",

    "$Table"."CORTE_APRENDIDO" as "CORTE_APRENDIDO",

    "$Table"."CONTEO_PESO" as "CONTEO_PESO",

    "$Table"."CONTEO_LONGITUD" as "CONTEO_LONGITUD",

    "$Table"."FECHA_ULTIMO_APRENDIZAJE" as "FECHA_ULTIMO_APRENDIZAJE"

from "POWERBI"."10x10_CONTEOPIEZAS_APRENDIZAJE" "$Table")

)

"t1"

)

"t1"

left outer join

(select "_"."ID_LOTE" as "ID_LOTE",

    "_"."FECHA_HORA" as "FECHA_HORA",

    ("_"."CODIGO" || '-') || "_"."VERSION_NUMBER" as "CODIGO_VERSION",

    "_"."VERSION_NUMBER" as "VERSION_NUMBER",

    "_"."CODIGO" as "CODIGO",

    "_"."STATUS" as "STATUS",

    "_"."ID_MAQUINA" as "ID_MAQUINA",

    "_"."TIEMPO_ESPERA" as "TIEMPO_ESPERA"

from "POWERBI"."10x10_LOTES" "_") "t3" on

(

"t1"."ID_LOTE" = "t3"."ID_LOTE"

)

)

WHERE

(

("t3"."FECHA_HORA" < TO_DATE('2017-03-04 00:00:00', 'YYYY-MM-DD HH24:Mi:SS'))

AND

("t3"."FECHA_HORA" >= TO_DATE('2017-02-02 00:00:00', 'YYYY-MM-DD HH24:Mi:SS'))

)

Status: Accepted
Comments
v-haibl-msft
Employee

@agcbridgestone

 

I’ve reported it internally to Power BI Team: CRI 32738064
I’ll post here once I get any update about it.

 

Best Regards,
Herbert

v-haibl-msft
Employee

@agcbridgestone

 

I’ve got response from the Product Team.

 

There is an issue due to a limitation in the level of SQL support on Oracle 11. This means that there are issues with the form of SQL generated by Power BI, when using calculated columns, or measures with scalar expressions. In many cases (at least for calculated columns, or measures like SUMX(Sales, Price*Qty) , it is possible to define such expressions as part of the query in the Query Editor, as a workaround. We will look to generate a different form of SQL when using Oracle 11 to avoid such issues.

 

Best Regards,

Herbert

Vicky_Song
Impactful Individual
Status changed to: Accepted
 
agcbridgestone
Frequent Visitor

Thank you very much for your support

olasimba
New Member

do you have any idea when this is going to be resolved?

 

mfattibello
Regular Visitor

Hi!

 

Same issue here, specifically on the Search field of a Slicer based on a Direct Query with Oracle database. On this way, I didn´t see a workaround for this scenario. Is there a forecast to fix this issue?

 

 


Thanks,
Fattibello

Anonymous
Not applicable

Hi ,

 

I am getting the same error on Oracle 11g. Any update for this issue?

 

Regards,

Maddy

htittoto
Frequent Visitor

Hi, 

 

I'm having the same issue here. It's been more than a year since someone last posted something. Does someone have an uptade on that issue?

 

Thanks,

 

htittoto