Numeric Filtering Fails on Some Values

[sckienle]

I have a problem with filtering numeric values.

I have a database table that defines a column as NUMERIC(18,1).  I try to filter this column to only the value 9.3 and the created query rounds that 9.3 number very poorly to return no values.  The filter in Power BI is:

Table.SelectRows(curriculum_db_course, each ([icoursemasterid] = 9.3))

in the SQL query this becomes: 

"_"."icoursemasterid" = 9.300000000000001E+000

9.3 is not the same as 9.300000000000001 obviously, so the database returns no rows. I tried changing the filter to explicity round the 9.3 number, Number.Round(9.3, 1), but this does not effect the generated SQL.

I can get around this by changing the type of the column in Power BI to a Fixed Decimal Number, which stops the query folding with the side effect of forcing Power BI to do more effort in processing the data.  I can also force this work around by changing the filter to:

Table.SelectRows(curriculum_db_course, each (Currency.From([icoursemasterid]) = 9.3))

But again this forces the evaluation into Power BI rather than passing it on to the SQL.

This is a simplified example, the real issue is being caused by larger use of buffered lists to filter data so the impact of having to change data types will become even more impactive on overall refresh performance.

Is there a query rollup acceptable way to address this problem?

[DataInsights]

@sckienle,

Have you tried custom SQL? That would ensure the query is sent to the database exactly as you define it.

[sckienle]

I try to avoid custom SQL unless necessary for performance.  I did get around this by recreating the process with a join rather than a more efficient buffered list / List.Contains option.  Would have been easier to have Power BI handle numbers correctly....