Unable to use direct query when importing from Snowflake

[EUGENEG]

Good day 

When Importing data from snowflake via Direct Query ,I am getting the following error 
'This step results in a query that is not supported in Direct Query Mode'
I am aware of the edit query limitations in Power Bi when importing in direct query ,but I have not applied any steps other than the standard navigation step

Advance editor details


let
    Source = Snowflake.Databases("pmxxx.east-us-2.azure.snowflakecomputing.com", "COMPUTE_WH", [CreateNavigationProperties=null, ConnectionTimeout=null, CommandTimeout=null]),
    DATAWAREHOUSE_Database = Source{[Name="DATAWAREHOUSE",Kind="Database"]}[Data],
    TEST_CAMP_Schema = DATAWAREHOUSE_Database{[Name="TEST_CAMP",Kind="Schema"]}[Data],
    CAMPAIGN_Table = TEST_CAMP_Schema{[Name="CAMPAIGN",Kind="Table"]}[Data]
in
    CAMPAIGN_Table

Any help will be gladely appreciated ,also note that I was able to perform this task last month ,but for some reason I can not anymore 

[HamidBee]

Hi @EUGENEG,

I was able to fix the following by firstly using Import mode instead of DirectQuery. Here are the full steps I followed:

No I need to use the EnableFolding=true command and this is only available when using the Value.NativeQuery function. I created a new step and typed the following:

= Value.NativeQuery( Source, "
SELECT OID.OrderID,O.OrderDate,UnitPrice*Quantity AS Total 
FROM Orders As O
FULL OUTER JOIN [Order Details] AS OID
ON O.OrderID = OID.OrderID", null, [EnableFolding=true])

Now if I apply any parameter based filters to the dataset it will work without any errors.

[v-kelly-msft]

Hi @EUGENEG ,

See the limitations about direct query below,be sure that your data is not limited in direct query mode.

There are currently a few limitations to using DirectQuery:

• If the Query Editor query is overly complex, an error occurs. To remedy the error, either delete the problematic step in Query Editor, or import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there's no Query Editor.

• Time intelligence capabilities are unavailable in DirectQuery. For example, special treatment of date columns (such as year, quarter, month, or day) isn't supported in DirectQuery mode.

• Limitations are placed on DAX expressions allowed in measures to ensure that queries sent to the underlying data source have acceptable performance.

• There's a one-million-row limit for returning data when using DirectQuery, unless using a Premium capacity. The limit doesn't affect aggregations or calculations used to create the dataset returned using DirectQuery. It only affects the rows returned. Premium capacities can set maximum row limits, as described in this post.

  For example, you can aggregate 10 million rows with your query that runs on the data source. The query accurately returns the results of that aggregation to Power BI using DirectQuery if the returned Power BI data is less than 1 million rows. If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit).

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

We have a Power BI connected to Snowflake using direct query with a DAX measure which returns a scalar value output. This DAX failes with row limit exceeded error due to more than 1M rows returned as part of the subquery in the DAX.

Question: Why does Power BI fire each sub query in the DAX separately and returns the rows instead of firing the entire query (like a stored procedure) on Snowflake so that only a scalar value is returned? Can this behaviour be changed with some setting so that the row limit error is not received.

[EUGENEG]

Thank you Kelly

However there is no transformation of data or dates 

Table has just 3 columns

Billing Type ID

Name

Waiting Days 

[v-kelly-msft]

Hi @EUGENEG ，

Based on your description,it seems an individual issue,better create  a support ticket via below link and our relevant personnel will help to handle it.
https://powerbi.microsoft.com/en-us/support/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[az38]

@EUGENEG 

DirectQuery and Import data is completely different methods. So, you can not modify data in Power QUery in DirectQuery mode

[EUGENEG]

@az38 
I am not modifying any data the only 2 applied steps are source and navigation to select a view 

[az38]

@EUGENEG 

It is correct. all you can do in Power query with direct query mode is to have a look at the data

[EUGENEG]

Hi Sorry this is a problem can not get over the first error promt on source as I need the report to be a direct report