PBIRS Jan2023 : TopN throwing error in Directquery report

parag_lokare — Wed, 19 Apr 2023 12:18:34 GMT

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

Re: PBIRS Jan2023 : TopN throwing error in Directquery report

d_gosbell — Thu, 20 Apr 2023 00:01:20 GMT

If this stopped working after the upgrade it sounds like it might be a bug and you should raise a support ticked with Microsoft to get this investigated.

topic PBIRS Jan2023 : TopN throwing error in Directquery report in Report Server

PBIRS Jan2023 : TopN throwing error in Directquery report

Re: PBIRS Jan2023 : TopN throwing error in Directquery report