I use subqueries in directquery mode, but power bi returns me with an error

[Anonymous]

Here is my query, can anyone please let me know what I did wrong? Or there's any workaround?

WITH

MC (Id,BrandId,RecordType, Classfication,marketplaceid) AS
(
SELECT
Id
,BrandId
,RecordType
,classification
,marketplaceid
FROM
MarketingCampaigns
),
totalsales (brand,brandid,datet,profit,totalsales) as
(
SELECT
[Name] brand,
brands.id brandid,
cast(MWSOrderItems.[PurchaseDate] as date) datet
,sum([ProfitHome_Amount]) Profit
,sum([QuantityShipped]*[ItemPrice_Amount]) TotalSales
FROM
MWSOrderItems,
NRProducts,
Brands,
MWSOrders
WHERE
SellerSKU = NRCode AND
BrandId = Brands.Id AND
MWSOrderItems.AmazonOrderId = MWSOrders.AmazonOrderId AND
OrderStatus = 'Shipped' AND
(TrackedOrderType IS Null OR TrackedOrderType = '0') AND
--brands.Name like 'mega%' and
SalesChannel = 'Amazon.com'
and MWSOrderItems.[PurchaseDate] is not null
--and [ProfitHome_Amount] is not null
and brands.isactive=1
and cast(MWSOrderItems.[PurchaseDate] as date)>'2017-08-31'
GROUP BY
[Name]
--,NRProducts.[ProductName]
,brands.id
,cast(MWSOrderItems.[PurchaseDate] as date)
),

SC (Date,Spend,Sales,MCId) AS
(
SELECT
StartDate Date
,TotalSpend_Amount Spend
,WeekOrderedProductSales_Amount Sales
,MarketingCampaignId MCId
FROM
SCCampaignPerformanceDetails
WHERE
StartDate>'2017-08-31'
),

AMS (Date,Spend,Sales,MCId) AS
(
SELECT
Date
,Spend_Amount Spend
,Sales_Amount Sales
,MarketingCampaignId MCId

FROM
AMSCampaignDetails
WHERE
Date>'2017-08-31'
),

NRPA (Date,Spend,Sales,MCId) AS
(
SELECT
Date
,TotalCost_Amount Spend
,Sales_Amount Sales
,MarketingCampaignId MCId

FROM
AMSProgramaticDetails
WHERE
Date>'2017-08-31'
),
B (Id,Name) AS
(
SELECT
Id
,Name
FROM
Brands
)

SELECT
B.Name
,datet
,Profit
,totalsales
,SUM(ISNULL(SC.Spend,0)) +SUM(ISNULL(AMS.Spend,0)) + SUM(ISNULL(NRPA.Spend,0)) MarketingSpend
,SUM(ISNULL(SC.Sales,0)) +SUM(ISNULL(AMS.Sales,0)) + SUM(ISNULL(NRPA.Sales,0)) MarketingSales

FROM
totalsales
left join mc on MC.BrandId=totalsales.brandid
LEFT JOIN B ON totalsales.BrandId = B.Id
LEFT JOIN SC ON MC.Id = SC.MCId
LEFT JOIN AMS ON MC.Id = AMS.MCId
LEFT JOIN NRPA ON MC.Id = NRPA.MCId

WHERE
totalsales.datet = SC.Date OR
totalsales.datet=AMS.Date OR
totalsales.datet=NRPA.Date
AND (MC.RecordType = 'Netrush Funded Ad Campaign' OR MC.RecordType= 'Partner Funded Ad Campaign')
AND MC.Classfication is null
AND MC.marketplaceid =2
--AND B.Name like 'mega%'
GROUP BY
B.Name
,totalsales.datet
,Profit
,totalsales

[Anonymous]

@Anonymous,

It doesn't support CTE in DirectQuery  mode , there is an idea about this issue, please vote it up.

In your scenario, you can use import mode as a workaround, or you can create a view in your database, then connect to this view in Power BI Desktop using DirectQuery mode.

Regards,
Lydia

[Anonymous · Accepted solution]

Thank you for your response! 

I found a workaround by rewriting the query.