Power BI Direct Query CAST to DECIMAL in DIVIDE function causing precision errors

[alimans3]

trafficstars

Describe the bug

I am running Power BI with Clickhouse connector and have a simple DAX measure:

SUMX(TABLE1, DIVIDE(TABLE1.COL1, RELATED(TABLE2.COL1)))

TABLE1.COL1 and TABLE2.COL1 are decimal(38,15) types.

TABLE2.COL1 (denominator) is losing precision and causing the multiplication to be wrong (multiplication by an integer rounded value)

Steps to reproduce

1. As mentioned above

Expected behaviour

Code example

Error log

Query log

Got from system.query_log:

SELECT OTBL.xxx, OTBL.xxx, OTBL.xxx, OTBL.xxx, OTBL.xxx, ITBL.xxx, multiIf( OTBL.T1COL1 IS NULL, NULL, multiIf( ( CAST(OTBL.T2COL1 , 'DOUBLE') IS NULL ) OR ( CAST(OTBL.T2COL1, 'DOUBLE') = _CAST(0., 'Nullable(Float64)') ), NULL, OTBL.T1COL1 / CAST( CAST(OTBL.T2COL2, 'DOUBLE'), 'DECIMAL' ) ) ) AS C1,

FROM .... (normal select with joins)

The problem comes from: CAST( CAST(OTBL.T2COL2, 'DOUBLE'), 'DECIMAL' )

CAST to DECIMAL added here is causing the column to be changed to an whole number (removing scale)

Configuration

Environment

• Driver version: 1.3.3.20250317
• OS: Windows 11, Clickhouse 25.4.2 in docker
• ODBC Driver manager: latest

ClickHouse server

• ClickHouse Server version: 25.4.2
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[alimans3]

with the latest version 1.4.1.20250523 The cast changed to:

CAST( CAST(OTBL.T2COL2, 'Float64'), 'DECIMAL' )

However the issue persists

[slabko]

Here is a minimal example for debugging:

1. Create and populate a new test table

create table test (a Decimal(9, 3), b Decimal(9, 3)) engine MergeTree order by a;
insert into test values (1, 1.3);

2. Start MS Power BI and import the table

3. Create a new measure for this table

Measure = SUMX(test, DIVIDE(test[a], test[b]))

4. Drop the new column on the canvas to see the result.

Notice that it shows the incorrect result: 1.00, while we expect it to be around 0.77.

This is not the result of rounding done by UI because we can inspect and execute the query to see exactly the same result (1.00)

select sum(`C1`) as `C1`
from
(
    select `a`,
        `b`,
        case
            when `a` is null
            then null
            else (case
                when cast(`b` as Float64) is null or cast(`b` as Float64) = 0.00
                then null
                else `a` / cast(cast(`b` as Float64) as Decimal)
            end)
        end as `C1`
    from `adventureworks`.`test`
) as `ITBL`

[slabko]

Also interesting how Power BI behaves when a and b are declared Float64 instead of Decimal(9,3) -- now I can see no type conversions in the query at all, which is probably expected.

[mshustov]

I believe we need to move it to PowerBI repository. It's likely to be a lack of mapping to Decimal type with precision. see https://github.com/exasol/powerbi-exasol/blob/e16793eb666361460ab83dfb46b5cadfd2002ab3/Exasol/Exasol.pq#L195

[alimans3]

https://github.com/exasol/powerbi-exasol/issues/49 created in exasol repo

[mshustov]

@alimans3 please create in https://github.com/ClickHouse/power-bi-clickhouse

[alimans3]

@mshustov https://github.com/ClickHouse/power-bi-clickhouse/issues/48