Error while using SQL queries in Power BI

[v-likosh]

Following query works fine within cosmosDB:
SELECT
COUNT(1) AS dcount_ip, c.AlertType, c.SubscriptionId, c.BinByDay
FROM(
SELECT
DISTINCT a.AlertObject.Metadata.cip AS d_cip,
a.AlertObject.AlertType,
a.AlertObject.ExtendedProperties.SubscriptionId,
DateTimeBin(a.ProcessingEndTimeUtc,'day') AS BinByDay
FROM Alert a
WHERE a.IsValidAlert = true) c
GROUP BY c.AlertType, c.SubscriptionId, c.BinByDay

However, when I am submitting this query through PoerBI SQL box at the time of fetching the data, it is failing.

Error message: 

DataSource.Error: Cross partition query only supports 'VALUE <AggregateFunc>' for aggregates.

[Uspace87]

Try to use Value Count(1) as per below:

SELECT
Value COUNT(1) AS dcount_ip, c.AlertType, c.SubscriptionId, c.BinByDay
FROM(
SELECT
DISTINCT a.AlertObject.Metadata.cip AS d_cip,
a.AlertObject.AlertType,
a.AlertObject.ExtendedProperties.SubscriptionId,
DateTimeBin(a.ProcessingEndTimeUtc,'day') AS BinByDay
FROM Alert a
WHERE a.IsValidAlert = true) c
GROUP BY c.AlertType, c.SubscriptionId, c.BinByDay

[v-likosh]

Hello @Uspace87 ,

The above code did not help in resolving the error and I still get the same error.

[v-yanjiang-msft]

Hi @v-likosh ,

Please see if the solution in the below similar thread can help:

DocumentDb error "Cross partition query only supports 'VALUE <AggreateFunc>' for aggregates" - Stack...

DocumentDb error Cross partition query only supports VALUE AggreateFunc for aggregates - SQL - YouTu...

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-likosh]

Hello @v-yanjiang-msft ,

The provided solution did not work in my case.