In Power BI, it appears CTE is not supported in Direct Query. In one report, I have various SUMs I need to do from a GROUP BY select. Thus, I tested that approach in SSMS and wanted to use that in Power BI. However, it is not possible. However, the query works as is in Import mode. In order to preserve the Direct Query ability, I have to adjust the query.

My query is involving some MAX() of several primary keys in the field results because of the GROUP BY clause.

When using CTE, a relation was done from the source table with the CTE initial query. This was a guarantee that the SUMs of various subqueries in the final query was having the data integrity validated.

Because of the Power BI restriction, I have to reformulate that query to avoid CTE. The first attempt was to adjust the query to have subquery as is but, despite the fact this produces the same result, I still feel uncomfortable with that approach because the relationship of the subquery is not guaranteed.

Here is a draft of the CTE approach :

;WITH CTETable (NoField,NoField2)
 AS (SELECT MAX(Client.NoField) AS NoField,MAX(Client.NoField2) AS NoField2 FROM Client GROUP BY Client.NoGroup)

SELECT CTETable.NoField,CTETable.NoField2,
 (SELECT COUNT(*) FROM Client WHERE CTETable.NoField=Client.NoField AND CTETable.NoField2=Client.NoField2) AS 'Field3'
 FROM CTETable

Here is a draft of the adjusted query which I am not sure if this is guaranteed to provide the same result :

SELECT MAX(Client.NoField) AS NoField,MAX(Client.NoField2) AS NoField2,
 (SELECT COUNT(*) FROM Client Client2 WHERE Client2.NoField=Client.NoField AND Client2.NoField2=Client.NoField2) AS 'Field3'
 FROM Client
 GROUP BY Client.NoGroup