Power BI SQL API Issue: Works in DAX Studio but receiving error in power BI.

[krutoileshii]

Failed SQL
On the server seems to run fine but fails in PowerBI Desktop with error Message:

1. OLE DB or ODBC error: [DataSource.Error] PostgreSQL: Received backend message BindComplete while expecting ParseCompleteMessage. Please file a bug..

2. Actual Cubes Attached as well.

dim_pbi_reports.yml
dim_pbi_workspaces.yml
fct_pbi_activities.yml

dim_date.yml
dim_dbt_resources.yml
dim_pbi_datasets.yml
dim_pbi_reports.yml
dim_pbi_users.yml
dim_pbi_workspaces.yml
fct_pbi_activities.yml
fct_pbi_workspace_membership.yml

 {
  "sql": "select \"rows\".\"basetable0.c40\" as \"c40\",
    count(1) as \"a0\"
from 
(
    select \"$Outer\".\"basetable0.c40\"
    from 
    (
        select \"_\".\"activity_type\" as \"basetable0.c40\",
            \"_\".\"__cubeJoinField\" as \"basetable0.c58\"
        from \"public\".\"activities\" \"_\"
        where \"_\".\"activity_type\" in ('CancelDatasetRefresh', 'AnalyzedByExternalApplication', 'Import', 'ConnectFromExternalApplication', 'DeleteComment', 'TookOverDataflow', 'UpdateApp', 'CreateReport', 'ViewDataflow', 'CreateEmailSubscription', 'DeleteEmailSubscription', 'DownloadReport', 'AddTile', 'DeleteDashboard', 'EditDataset', 'DeleteDataset', 'RunEmailSubscription', 'DeleteReport', 'ExportReport', 'ExportTile', 'ViewDashboard', 'GetWorkspaceEncryption', 'UpdateFolderAccess', 'CreateDataflow', 'EditReport', 'TakeOverDataset', 'CreateFolder', 'CreateDataset', 'GenerateDataflowSasToken', 'UpdateFolder', 'GetGroupUsersAsAdmin', 'ArtifactAccessRequest', 'GetUnusedArtifacts', 'GetPowerBIDataModel', 'CreateDashboard', 'GetDataflowUsersAsAdmin', 'RefreshDataset', 'PrintReport', 'PostComment', 'SetScheduledRefresh', 'ReadDataflow', 'GetDataflowDatasourcesAsAdmin', 'RequestDataflowRefresh', 'UpdateEmailSubscription', 'GetPowerBIDataModelDiagramLayouts', 'UpdateDatasetParameters', 'ShareReport', 'ShareDataset', 'CancelDataflowRefresh', 'ViewReport', 'UpdateDataflow', 'UnpublishApp', 'ExportArtifact', 'AnalyzeInExcel')
    ) \"$Outer\"
    inner join 
    (
        select \"rows\".\"__cubeJoinField\" as \"semijoin1.c58\"
        from 
        (
            select \"_\".\"__cubeJoinField\"
            from \"public\".\"workspaces\" \"_\"
            where \"_\".\"workspace_type\" = 'Workspace'
        ) \"rows\"
        group by \"__cubeJoinField\"
    ) \"$Inner\" on (\"$Outer\".\"basetable0.c58\" = \"$Inner\".\"semijoin1.c58\" or \"$Outer\".\"basetable0.c58\" is null and \"$Inner\".\"semijoin1.c58\" is null)
) \"rows\"
group by \"basetable0.c40\"
limit 1000001"
}

Logical Plan
N/A

Tool
Power BI using SQL API

Version:
Latest

Additional context
The error only occurs inside PowerBI, Works just fine in dax, web interface, etc.

Here is a working DAX Query in DAX Studio

/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
    workspaces[workspace_name],
    workspace_reports[report_name],
    activities[report_id],
    workspace_reports[count],
    activities[count],
    KEEPFILTERS( TREATAS( {"Workspace"}, workspaces[workspace_type] )),
    KEEPFILTERS( TREATAS( {"ViewReport"}, activities[activity_type] ))
)
ORDER BY 
    workspaces[workspace_name] ASC,
    workspace_reports[report_name] ASC,
    activities[report_id] ASC,
    workspace_reports[count] ASC,
    activities[count] ASC
/* END QUERY BUILDER */

[igorlukanin]

Hi @krutoileshii 👋

I see that Power BI is used with the SQL API here. This is indeed an unsupported combination. Please consider using the DAX API (Cube Cloud only).

I also see you're trying to jin multiple cubes in Power BI. This is also visible by the way __cubeJoinField is used in the generated SQL. I'd recommend to not do that. Instead, use views and refrain from using explicit joins.

I hope this helps!