I'm using a native query that contains a CTE to create a table in SSAS. When I use the CTE for a SSAS model in IMPORT mode, it works perfectly well. But when I use the same CTE in SSAS model in DIRECT QUERY mode, I am able to deploy the model on SQL Server but then when I try to query data from SSMS or DAX Studio I get an error as below
Executing the query ... OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Incorrect syntax near the keyword 'with'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. Incorrect syntax near ')'.. Run complete

Can someone help me understand whether CTE is not supported in SSAS DIRECT QUERY mode? I couldn't find any relevant info online.

;with crmhfCTE as (
SELECT     
    lp.d_Book_Date as Transaction_Date
    ,hfa.OfferSelectedBase as Funding_Amount
    ,hfa.RealEstateActualPrice as Funded_Asset_Purchase_Amount
    ,N'شراء جاهز' as Product_type  
    ,hfa.PropertyType as Property_Type_Code
    ,(select lookupvalue  from CRM_Lookups  as lup where 1=1 and hfa.PropertyType = lup.lookupcode and  LookupType = 'vrp_propertytype' )  as Property_Type_Name
    ,hfa.SellerName as Seller_Data
    ,hfa.CustomerName as Customer_Name
    ,'750' as VAT_Amount
    ,hfa.ApplicationID as Transaction_Number
    ,hfa.FirstTimeHouseBuyer as First_Time_House_Buyer_Flag
    ,'-' as Annual_Statement_Of_Sold_Debts
    ,lp.v_type as Loan_Type
    ,lp.d_Extraction_Date as Extraction_Date
FROM HF_Application as hfa
RIGHT JOIN(
    SELECT 
        v_Application_Num
        ,d_Book_Date
        ,v_Type 
        ,d_Extraction_Date
    FROM Retail_Loan_Contract_Hist
        WHERE 1=1
        and v_Type IN ('RCMF')
) as lp on hfa.ApplicationID = lp.v_Application_Num

where 1=1 
and hfa.IsCurrent = 'y'

)

select 
    Transaction_Date as 'Transaction Date'
    ,Funding_Amount as 'Funding Amount'
    ,Funded_Asset_Purchase_Amount as 'Funded Asset Purchase Amount'
    ,Product_type  as 'Product Type'
    ,case Property_Type_Name
        when 'Ready Built Duplex' then N'دبلكس'
        when 'Ready Built Villa' then N'فيلا'
        when 'Ready Built Apartment' then N'شقة'
        when 'Ready Built Building' then N'مبنى'
        else Property_Type_Name  end as  'Property Type Name Arabic'
    ,Seller_Data as 'Seller Data'
    ,Customer_Name as 'Customer Name'
    ,VAT_Amount as 'VAT Amount'
    ,Transaction_Number as 'Transaction Number'
    ,First_Time_House_Buyer_Flag as 'First Time House Buyer Flag'
    ,Annual_Statement_Of_Sold_Debts as 'Annual Statement Of Sold Debts'
    ,Loan_Type as 'Loan Type'
    ,Extraction_Date as 'Extraction Date'
from crmhfCTE