Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Hello @Uspace87 ,
The above code did not help in resolving the error and I still get the same error.
Hi @v-likosh ,
Please see if the solution in the below similar thread can help:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.