Weird ODBC Gateway Failure

[sckienle]

I am having an odd error when publishing a report using and ODBC source and the Power BI service connecting it to the gateway.

The gateway ODBC source is set up properly.  If I have a Power BI report which uses that source alone, I can connect it to the gateway when published to the service.

If I reference another data source, but keep them separate in the M code, the gateway can be connected as well.  What I mean by "separate" is that the two sources are not used in the same table in the Power BI M code.

But, as soon as I use both datasources in the same M code for a table, whether by a join, a List.Contains, or other activity, when I publish I can no longer connect to the gateway.

Has anyone seen this behaviour before and is there a way to correct it?

Thanks

[v-frfei-msft]

Hi @sckienle ,

Did you use on prem or personal gateway ? Could you please share more details about your issue? like screenshot of the error message or something else.

[sckienle]

@v-frfei-msft Any update on this?  I am starting to perform more and more DAX back flips to get the work done that should have been accomplished in the data layer rather than the analytical layer.

Thanks,

     Steve

[sckienle]

This is using the on premises gateway.  I do not get an error message, just don't see the gateway:

This is the code that will show the gateway:

Let
  Source = Odbc.DataSource("dsn=FIS", [HierarchicalNavigation=true]),
  WMED_PROD_Database = Source{[Name="WMED_PROD",Kind="Database"]}[Data],
  int_Schema = WMED_PROD_Database{[Name="int",Kind="Schema"]}[Data],
  employee_vw_View = int_Schema{[Name="employee_vw",Kind="View"]}[Data]
in
  employee_vw_View

But if I add a reference to a buffered list from a different datasource, with the following code, the gateway is not available, as shown above:

let
  Source = Odbc.DataSource("dsn=FIS", [HierarchicalNavigation=true]),
  WMED_PROD_Database = Source{[Name="WMED_PROD",Kind="Database"]}[Data],
  int_Schema = WMED_PROD_Database{[Name="int",Kind="Schema"]}[Data]
  employee_vw_View = int_Schema{[Name="employee_vw",Kind="View"]}[Data],
  #"Buffer Known IDs" = List.Buffer(#"Known UserIDs"),
  #"Filtered Rows" = Table.SelectRows(employee_vw_View, each List.Contains(#"Buffer Known IDs", [id_1]))
in
  #"Filtered Rows"

Some context:

• I have the #"Known UserIDs" query in the working report, I just don't reference it in the query with the pull form the ODBC datasource
• THE ODBC connection is pretty basic but does reference a PostgreSQL database hosted on AWS: ....us-west-1.rds.amazonaws.com
• The #"Known UserIDs" query directly connects to an internally hosted PostgreSQL database

I hope this helps.  Let me know if and how to get you more information if needed.  As I said, there is no error reported, just not being able to use the gateway.

Steve