We have a Direct Query Report running on PBIRS Jan2023. On the report we have a visual that displays top 20 values by a category. The Top 20 is set in the Filter Pane for the visual. Earlier to Jan2023, this was working fine. But after Jan2023 upgrade , the visual throws error "TopN with Ties clause is not allowed without a corresponding Orderby clause."
We did refer to the Feature summary Power BI Report Server January 2023 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI. The measure is already set to DistinctCount.
The report has 3 datasets RV , AL and PS, The relationship is set as RV (1:M) AL ( direction both) , RV (1:M) PS (direction both).
The visual has category and value from the AL dataset.
The visual does not throw any error until we add a report level filter (filter on all pages) from the PS dataset.
I captuared the query that was generated using SQLProfiler. Below is the easy to understand version of that query. As you see there are two WithTies and the inner one is missing the orderby clause.
SELECT TOP (20) WITH TIES *
FROM (
SELECT TOP (20) WITH TIES [basetable0].[c12] ,COUNT_BIG(DISTINCT [a0]) AS [a0]
FROM (
SELECT v.VID [c2], CDSID [a0],AckBy [c12] FROM AL a
INNER JOIN RV v ON v.[VID] = a.[VID]
INNER JOIN PS p ON v.[VID] = p.[VID]
WHERE p.[PSID] = 1323
) [basetable0]
GROUP BY [basetable0].[c12]
) AS [MainTable]
ORDER BY [a0] DESC
