Simple page-level filter on Amount greater than 100 not working with Direct Query??

[dvp]

from https://github.com/MicrosoftDocs/azure-docs/issues/49482 in case someone here can help: Hi, I have a simple table .create table TestTableForComparison (amount:decimal , id: string); .ingest inline into table TestTableForComparison <| 10, "trx1" 11, "trx2", 12, "trx3", 13, "trx4" When I Get Data in Power BI with Direct Query, and put a simple Table visualization on a Report, and try to filter the page filter Amount with greater than 10 I get an error OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source Why is that? Best regards, Deyan

[dax]

Hi @dvp , 

I can't reproduce your sample in my environment, so if possible, could you please inform me more detailed information(such as your connector, your sample data  and how you operate this in powerbi)? Then   we could help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dvp]

@dax , it is (unfortunately) very easy to repro it: 

1) Power BI Desktop -> Get Data -> Azure -> Azure Data Explorer (Kusto) -> Connect

2)

Cluster: https://help.kusto.windows.net

Data Connectivity mode: DirectQuery

3) Choose Samples -> StormEvents table -> Load

4) Click on Table visual, then click on Fields EventNarrative and DeathsDirect

5) In the Filters vertical pane, expand DeathsDirect filter, choose for example "is greater than or equal" 10, click Apply filter, and enjoy the error 😉

[artemus]

Looks like this is a bug in the March version of Power Bi. Have you tried the Febuary version?

[artemus]

I think you can work around this by either changing the aggregate of the amount column to don't summerize, or drag amount directly onto the filter pane from the fields list.

[dvp]

Hi @artemus , neither of these workarounds seem to work, unfortunately. The only one working is getting data from Azure Data Explorer using Azure SQL Database connector, which probably has some other peculiarities.

[artemus]

I'm trying to get a fix rolled out, but unfortunatly, it isn't a simple one to do.

Not sure why the work arounds won't work though, if you remove aggregation on the columns it should work just fine. If you can send me an example using the help cluster, I will look into it.

[dvp]

@artemus , I tried with DeathsDirect not summarized and still get the error: https://github.com/deyanp/power-bi-page-filter-error-repro.git

[artemus · Accepted solution]

Looks like you found another bug (fix is pending review)...

You can get around this one by going to Options -> Current file -> Regional settings. Then change it to English (US). It looks like it was translating 5 to 5,0 which isn't a valid number in KQL.

[dvp]

Thanks, @artemus , that seems to work!

An additional question - how did you get to the raw KQL sent to ADX? If I know how to do that I could troubleshoot such issues in the future by myself ..

[artemus]

If you turn on tracing in the diagnonstics under options, the log files will include it.

[artemus]

Actually, the fix for the locale issue is already reviewed, it should be included in the next rollout of Power Bi.

[artemus]

I can repo it:

05T05:28:18.8690108Z","Action":"Engine/Module/Kusto/IO/Kusto","HostProcessId":"29372","Message":"OnSelectRows: [Reason=Expression.Error,Message=Invalid placeholder in format string.,Detail=[Offset=10,Reference=1,Length=4]]","ProductVersion":"2.79.5768.521 (20.03)","ActivityId":"a9a32e68-6e62-49f3-b924-7ed8a0e4c6bf","Process":"Microsoft.Mashup.Container.NetFX45","Pid":30036,"Tid":1,"Duration":"00:00:00.0002448"}

Seems to be related to a bad call to Text.Format, which is happening when Direct Query does a GroupBy with an aggregate column of List.Sum([amount]). This causes the new column to have any type, which confuses Kusto into thinking the column is of type string. The error then occurs when it does a column comparison with one of the operators: =, <, <=, >, >=, but does not occur with operators: <>, & But I'm not sure why this would cause the exception it does.

This is fixable on the dax to M side by changing List.Sum([amount]) to List.Sum([amount], number)

[dvp]

@artemus , I am not a pro Power BI user (I mean knowledge-wise, not license), can you give me detailed instructions how to apply the workaround you mentioned "This is fixable on the dax to M side by changing List.Sum([amount]) to List.Sum([amount], number)"?

On which screen / menu / pane should I change this formula?