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.
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'))
)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.