Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
parag_lokare
Advocate I
Advocate I

PBIRS Jan2023 : TopN throwing error in Directquery report

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

1 REPLY 1
d_gosbell
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.